--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@u...> wrote:

>
>
> > > What is recommended when using foreign keys (apply all fk's or apply
> > > fk where it's most needed?)?
> >
> > There are two sides pulling against each other. The purist would
> > demand each FK explicitly defined, and there is good argument for
> > this.
>
> Indeed there is.
>
> >If you do not define the FK, then you must write custom triggers
> > to emulate a FK, and risk inconsistent data if you do it wrong. At
> > least with FK's defined, you can not possibly store a child record
> > without a parent record to reference.
>
> Triggers won't work properly because of transaction isolation.
>

Good point Martijn. If you have multiple simultaneous transactions,
then your triggers may not realise that the parent record has been
deleted by a transaction that is outside your isolation visibility.

Of course a lot of systems that have a batch insert mode don't have
multiple concurrent transactions so it might still be an option in
those cases.

> > The other side of the argument (realist) is that the purist has his
> > head in the sand. You are dealing with the performance implications of
> > an implementation decision within Firebird, and in some cases, this
> > will slow it down enormously.
>
> Rubbish. If the implementation (in this case Firebird) can't bring
> performance
> because of a logical issue (the constraint), then the developer

should bark

> at the company/people who implement the system (Firebird developers) and
> demand better performance :-)
>
> Guess what happened -- Firebird 2 gives you much better performance on
> indices with a large number of duplicates. Problem solved.
>

Yes, but FB 2 is still beta, so problem will be solved sometime
hopefully very soon, but not yet unless you are prepared to do a
production release on a beta database.

> > A "compromise" would be to define the FKs, but to drop the constraints
> > during the batch insert if they were really causing an important
> > performance bottleneck, but then you would need to make sure the data
> > you import is "clean".
> >
> > I find it is good to be a purist at heart, but to accept that there
> > are times when you may have to be a realist about it.
>
> IMO, I would drop a bit of performance (not talking about bulk insert
> modes) for consistency every day...

ditto. The difference between waiting 5 minutes or 10 minutes for a
process is actually not as significant in most business cases.
Particularly if you then lose that 5 minutes later on when someone has
to manually fix the inconsistent data.

Adam