At 11:03 PM 03-04-01 +1000, you wrote:

> > Foreign keys are good to use for many master-detail and
> > parent-child relationships and for intersection tables;
> > and evil to use for lookup relationships.
>
>That last line I would change to...
>
>"inappropriate to use for lookup relationships in the current versions
>of Interbase".
>
>
>There is nothing conceptually wrong with using foreign keys for
>lookups, in fact it is technically correct. It is just that IB does
>not provide the means to implement this as efficiently as it should.

I disagree that it is a "technically correct" application of a foreign key relationship. It implies that the table being referenced is master to the table referencing it. This is clearly not conceptually correct. Please follow through my argument below as it is important to understanding why a foreign key relationship for lookup is definitely going to cause problems.

>Hopefully this may change in future versions.

When you use FOREIGN constraints for control tables, IB creates a single-column index of low selectivity on the column on which the FOREIGN constraint is applied. It has been mooted to drop the auto-creation of the foreign key column index to avoid certain problems they can cause for the optimizer. You will still need a valid index on a FOREIGN key column but it will allow you to avoid duplication and to name the index **for the key** yourself.

To get a useful index on a control column, you need to include the primary key (or another column of high selectivity); otherwise, the performance of joins and sorts is crippled. In that case, the foreign key relationship would have to be formed by having a composite primary key in the lookup table, to match the foreign key in the referencing table. This is NOT feasible, since lookup tables have to exist without dependencies on the tables that use them.

This selectivity problem on control keys is not peculiar to InterBase, i.e. it is not a implementation problem that can be changed by "improving the structure". It is a logical problem in the relational model.

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________