Subject Re: [IBO] Foreign Key
Author Helen Borrie
At 10:47 AM 03-04-01 +0200, you wrote:
> I know this is not the right group to ask, but I posted this message twice
>in the ib-support list, no one answered.

Wrong, I answered in great detail.

>Maybe it's a dummy question,
>anyway, not everybody can be a "master", so if no one mind, I would like to
>ask this here. Maybe someone can answer me privately ? I have a Customer
>table, and normally I could define 6 foreign keys to that table : Country,
>language, coin, payment conditions.... Those fields are referencing fields
>in other tables. Should I define those 6 foreign keys ? Or could I have
>a performance problem after that ??

No, don't define them formally as Foreign keys. You will get 6 relationships with low selectivity indexes and it will kill performance.

Only use the FOREIGN constraint for master-detail or parent-child (tree) relationships. Hand-code the cascade triggers for lookup relationships such as those you mention.

Don't put single-column indices on those columns, either. Create composite indices for them, each consisting of the lookup key and the primary key (in that order).

>Is foreign keys a good thing to use ?

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.


All for Open and Open for All
InterBase Developer Initiative ยท