Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Unanswered: Index creation error.....

    Dear All,

    I have a table A with fields 'id' (primary key) and 'name', i have 3 tables b, c and d which have the common columns 'id' (foreign key from table a) and 'name'. The column 'id' is also the primary key in tables b, c and d. I have designed my database in such a way that all my tables are created in one dbspace and all my indexes in other dbspace. I have given my names for index and constraints. The reason is when i create a primary key constraint for a table, informix by default creates a unique index and assign its own id. The syntax is -


    For table b -

    -- To create unique index and primary key

    create unique index b_idx on b(id) using btree in mydbspace

    Alter table b add constraint (primary key (id) constraint b_pk


    -- for foreign key referring to table a

    -- index for foreign key

    create index b_idx2 on b(id) using btreee in mydbspace

    -- foreing key constraint

    alter table b add constraint (foreign key(id) references a
    constraint b_fk2)

    (The same statements would follow for table c and d)

    When i execute the above statments i get an error - 350, Index already exists on a column. (only 2 index can exist on a column). Two indexes were already created for column 'id' one for unique index and one for primary key constraint. I cannot go ahead and create the indexes
    for foreign key. Any ideas how tackle this problem. Thanks in advance.

    Best Regards,

    Lloyd

  2. #2
    Join Date
    Jul 2003
    Location
    Calcutta, India
    Posts
    42
    I am sorry but I didn't get your complete problem. You have already created 2 index (informix allows only 2 index to be created in a column or combination of columns one ASC and one DSC) then why you require third index? The first index you created is a unique index and the second one is being created by INformix. For the first one u gave a name but for the second index the Informix will give its own name.
    In fact you don't even require to create a unique index just a primary key will do. Can you please let us know the reason why u want to create 3 index with 3 different names in the same column of the same table?

    I apologize for my ignorance.

    Thanks
    adityanlal

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Index creation error.....

    Originally posted by lloydnwo
    Dear All,

    I have a table A with fields 'id' (primary key) and 'name', i have 3 tables b, c and d which have the common columns 'id' (foreign key from table a) and 'name'. The column 'id' is also the primary key in tables b, c and d. I have designed my database in such a way that all my tables are created in one dbspace and all my indexes in other dbspace. I have given my names for index and constraints. The reason is when i create a primary key constraint for a table, informix by default creates a unique index and assign its own id. The syntax is -


    For table b -

    -- To create unique index and primary key

    create unique index b_idx on b(id) using btree in mydbspace

    Alter table b add constraint (primary key (id) constraint b_pk


    -- for foreign key referring to table a

    -- index for foreign key

    create index b_idx2 on b(id) using btreee in mydbspace

    -- foreing key constraint

    alter table b add constraint (foreign key(id) references a
    constraint b_fk2)

    (The same statements would follow for table c and d)

    When i execute the above statments i get an error - 350, Index already exists on a column. (only 2 index can exist on a column). Two indexes were already created for column 'id' one for unique index and one for primary key constraint. I cannot go ahead and create the indexes
    for foreign key. Any ideas how tackle this problem. Thanks in advance.

    Best Regards,

    Lloyd
    Hi Lloyd,

    I think that the problem is the tree table have the same primary key.
    So, for generate foreign key in another tables, create unique index without primary key.
    I think that review the normalized table.

    Happy year 2004.

    Gustavo.

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

    Re: Index creation error.....

    Originally posted by gurey
    Hi Lloyd,

    I think that the problem is the tree table have the same primary key.
    So, for generate foreign key in another tables, create unique index without primary key.
    I think that review the normalized table.

    Happy year 2004.

    Gustavo.
    Hi Adityanlal / Gustavo,

    Happy new year to you too. Thanks for your feedback.
    I wanted to create these indexes in different dbspaces thats the reason i gave a name to it. I don't want informix to give a name for the indexes, later on it will be difficult for me to keep track of them, especially when i want to drop them.

    create unique index b_idx on b(id) using btree in mydbspace

    The first statement will create a unique index. This is a foreign key referring to table 'A', this will also be unique within the table

    Alter table b add constraint (primary key (id) constraint b_pk)

    This will create a primary key constraint, i have named the constraint.


    Now for the foreign key constraint referring to table A

    create index b_idx2 on b(id) using btreee in mydbspace

    -- foreing key constraint

    alter table b add constraint (foreign key(id) references a
    constraint b_fk2)

    Since informix allows only to indexes i will have to delete teh foreign key constraint.

    Best Regards,

    lloyd

Posting Permissions

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