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/