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

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

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