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

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

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


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com