Subject RE: Helen [IBO] Foreign Key
Author Nico Callewaert
Sorry Helen, I didn't see the mail, thanks a lot, but one more question
then. The main reason I was planning to use foreign keys was to be sure
that the data entered was correct. For example payment conditions. With
that foreign key I would be sure that when I enter the payment condition in
the customers table, that the value entered would reference the primary key
in the payment conditions table. And when a user would attempt to delete a
record in the payment conditions table, that is used in the customers table,
he will receive a error.
Now I accomplish all of this in triggers. Before there is a record entered
in the customers table, I have a pre-insert trigger that checks if the
payment condition is valid, and for the payment conditions table, I have a
before delete trigger with a IF (EXISTS.... statement to check if that
condition is used in the customers table. Is that a good thing then ?

Thanks, Nico
-----Oorspronkelijk bericht-----
Van: Helen Borrie [mailto:helebor@...]
Verzonden: dinsdag 3 april 2001 14:29
Onderwerp: Re: [IBO] Foreign Key

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
>ask this here. Maybe someone can answer me privately ? I have a
>table, and normally I could define 6 foreign keys to that table :
>language, coin, payment conditions.... Those fields are referencing
>in other tables. Should I define those 6 foreign keys ? Or could I
>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


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

Yahoo! Groups Sponsor

Click Here to Find Software Faster

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

[Non-text portions of this message have been removed]