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
Aan: IBObjects@yahoogroups.com
Onderwerp: Re: [IBO] Foreign Key


At 10:47 AM 03-04-01 +0200, you wrote:

>Hi,
>
> 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.

Cheers,
Helen

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

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]