Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: Index already exists

    Hello everybody,

    I am having the following problem:

    database …;
    Database selected.

    drop index fxindex01;
    319: Index does not exist.

    111: ISAM error: no record found.
    Error in line 1
    Near character position 19


    drop index 195_1387;
    201: A syntax error has occurred.
    Error in line 1
    Near character position 12


    create index fxindex01 on tabel
    ( foreign_key )
    in tabel;


    By trying to create a new index, I´ve got the error notification that it already exists, but trying to use dbaccess it said that it didn´t exist. By trying to create it, dbaccess says again, that it already exists, but it is also not possible to drop it.
    Is there anybody who is able to let me know what´s the problem ? thanx in advance

    Kerstin

  2. #2
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Re: Index already exists

    Originally posted by KerstinS
    Hello everybody,

    I am having the following problem:

    database …;
    Database selected.

    drop index fxindex01;
    319: Index does not exist.

    111: ISAM error: no record found.
    Error in line 1
    Near character position 19


    drop index 195_1387;
    201: A syntax error has occurred.
    Error in line 1
    Near character position 12


    create index fxindex01 on tabel
    ( foreign_key )
    in tabel;


    By trying to create a new index, I´ve got the error notification that it already exists, but trying to use dbaccess it said that it didn´t exist. By trying to create it, dbaccess says again, that it already exists, but it is also not possible to drop it.
    Is there anybody who is able to let me know what´s the problem ? thanx in advance

    Kerstin
    Hi Kerstin,

    Are u sure its an index or a constraint, because for every constraint you add, informix creates an index. You can locate the constraint name from dbaccess.

    To drop a constraint
    Alter table <name>
    drop constraint <name>

    Regards,

    lloyd

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Hi lloyd,

    I just looked it up again, but it´s definetly an index. Do you have any other suggestions concerning the problem? thanx

    Kerstin

  4. #4
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Your attempt to drop index 195_1387 leads me to believe that you are trying to drop one of the "manufactured" index names that Informix creates when an unnamed constraint (ie, Primary Key, forign key, check) has been declared. In reality, there's a space in from of the 195, and you can't drop the index. You have to determine the constraint and drop that, which will drop the index.

    There's ways of searching system tables but you might just try:

    dbschema -d mydbname -t mytablename -ss
    Fred Prose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •