Unix Technical Forum - View Single Post

#2 (**permalink**) 04-19-2008, 09:15 PM

Basically: primarty keys use unique indexes while foreign keys use non-unique indexes. That's why you need separate indexes for your
table.

Gorazd

"Bill Dare" <dareb@jevic.com> wrote in message news:c037sp$a85$1@terabinaries.xmission.com...
>
> 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
>
>
> sending to informix-list

Thread: foreign keys and indexes View Single Post

Thread: foreign keys and indexes
View Single Post