foreign keys and indexes

#1 (**permalink**) 04-19-2008, 08:15 PM

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 ,

#2 (**permalink**) 04-19-2008, 08: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

#3 (**permalink**) 04-19-2008, 08:15 PM

Gorazd Hribar Rajteriè wrote:
> Basically: primarty keys use unique indexes while foreign keys use
> non-unique indexes. That's why you need separate indexes for your
> table.

AFAIK, there are only two reasons that a (non-unique) index is created
on a foreign key column (or set of columns in general), and those are:

1. When someone updates the corresponding primary key column(s), the
RI checking does not have to do a sequential scan of the
referencing table to see whether there are any records that will
be left without a parent record, and
2. When someone deletes a record in the referenced table, the RI
checking won't have to do a sequential scan, for the same reason.

For both of these, the primary key index would suffice - with minimal
loss of efficiency and a considerable gain in disk space not used.

I think the original counter-argument was that an RI constraint should
not use user-defined indexes that don't match exactly because a user
could drop the index. I'm not sure whether that's valid; it could
refuse to allow the index to be dropped until an alternative was in
place, or it could automatically create the necessary index.

>
> "Bill Dare" <dareb@jevic.com> wrote:
>>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

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode