foreign keys and indexes

Posted in 2004

Bill Dare — Sat, 7 Feb 2004 12:05:26 -0500 — source: Usenet: comp.databases.informix

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C3ED9C.94F4AD10 Content-Type: text/plain AIX 5.2 IDS 9.40.FC3 I have 2 tables, fbheader and fbdetail create table fbheader ( bill_num serial not null , . . . primary key(bill_num) ) create table fbdetail ( bill_num int line_num int . . . primary key(bill_num,line_num) ) alter table "informix".fbdetail add constraint (foreign key (bill_num) references "informix".fbheader on delete cascade); When I create the foreign key constraint the server creates an index on bill_num. So I have 2 indexes with a lead column of bill_num. I have always assumed that I was stuck with this situation. The server will not use the existing composite index for the foreign key. Is this true? Is there any way to force the use of the existing index? I have this same situation in several other relationships. It is giving me problems now with the optimizer. Regards, Bill ------_=_NextPart_001_01C3ED9C.94F4AD10 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2653.12"> <TITLE>foreign keys and indexes</TITLE> </HEAD> <BODY> AIX 5.2 IDS 9.40.FC3 I have 2 tables, fbheader and fbdetail create table fbheader   (     bill_num serial not null ,         .         .         .     primary key(bill_num) ) create table fbdetail   (     bill_num int     line_num int         .         .         .     primary = key(bill_num,line_num) ) alter table "informix".fbdetail add = constraint (foreign key (bill_num)     references = "informix".fbheader  on delete cascade); When I create the foreign key constraint the server = creates an index on bill_num.  So I have 2 indexes with a lead = column of bill_num.  I have always assumed that I was stuck with = this situation.  The server will not use the existing composite = index for the foreign key.  Is this true?  Is there any way = to force the use of the existing index? I have this same situation in several other = relationships. It is giving me problems now with the optimizer. Regards, Bill </BODY> </HTML> ------_=_NextPart_001_01C3ED9C.94F4AD10-- sending to informix-list