Nico:

I don't know alot abput foreign keys under IB, but I can offer you a
couple of cents (okay, so my Canadian cents aren't worth much right
now, but I will offer them anyway).

On 3 Apr 2001, at 10:47, Nico Callewaert 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. 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 ?? Is foreign keys a good thing to use ?
>

Foreign keys are used to give the optimizer clues as to the structure
of the data (as opposed to the database). It uses the clues as a way
to determine it's plan for the query. Normally foreign keys are like
a gold brick (nice to have, but you don't really need it), in fact
some databases don't even have them (MySQL is an example)).

However if you have a query where the plan is really inefficient,
then you can use FKs to give the optimizer a better handle on the
data, so it can develop a better plan.

As to performance problems, you have to make sure that you don't
confuse a foreign key, with an index. Indexes can affect
performance, they will make an INSERT or DELETE slightly longer, we
are talking something like 1 sec versus 1.02 sec, it's enough that it
could affect servers with heavy loads, particularily bulk inserts and
purges. An FK can give the optimizer a clue to use a particular
index, but does not mean that there is an index.

Paul


Paul Schmidt,
Tricat Technologies
Email: paul@...
Website: www.tricattechnologies.com