Subject Re: [firebird-support] Re: Poor selectivity in foreign keys
Author Ann W. Harrison
At 05:51 PM 10/15/2004, johnmancuk wrote:

>My understanding is that you can't use sprocs, triggers or check
>constraints to maintain referential integrity on FB (at least
>reliably, in a multi-transaction situation) because they don't
>use 'magic' to see through your current transaction context. Indexes

More or less true. But like most computer 'magic' respecting or
ignoring transaction context is just a question of how the check
is coded. There's nothing inherent in indexes that makes them
more powerful than anything else.

Before the great schism, I talked to Borland's developers about
the requirement that all foreign key constraints be supported with
an index on the child table. They said that without that index
deletes from and updates to the parent table are very slow. So
child table indexes went from optional, to automatically created,
to being a part of the compile/optimize logic.

SQL and relational theory consider indexes in the same light as
table spaces - physical attributes of a database that have no
semantic relevance, are not discussed in the standard, and which
can be added or deleted without affecting programs - except for
their performance, of course. I'd like to see Firebird return
to that model for foreign keys.