Subject Re: Poor selectivity in foreign keys
Author johnmancuk
Thanks Ann, you keep comming to my rescue! I'll buy you a drink one
day if I'm ever in your timezone ;)

I know I've written apps over the years that are theoretically
broken - checks in sprocs/triggers that could return incorrect
results because of other transactions. I should probably use TABLE

But I agree, greater availability of the 'magic' would be useful.
Even if it is slow.

What about deferring all RI checking to the commit point? That would
remove the need for magic, and allow custom RI too. It would seem
more elegant, and just as functional in a short-transaction world
(we all live there, right?!)

Could this be FireChick 1.0 (tm)??? LOL


--- In, "Ann W. Harrison"
<aharrison@i...> wrote:
> 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.
> >do.
> 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.
> Regards,
> Ann