Index Creation Error........

[mynwo]

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