At 10:37 AM 17/10/2004 -0300, you wrote:

>Helen Borrie wrote:
>
> > Sorry, I don't understand what you are saying here. Custom RI is the
> >
> >*alternative* to creating a foreign key, i.e. declarative RI, in situations
> >where the effect of declarative RI is worse than the risk of a RI
> >violation. You would not have both a FOREIGN KEY constraint *and* custom
> >RI on this relationship.
> >
> >
> >
>What i mind is, why i need that index in custom RI. For a better
>performance?

Yes. It is not the index that enforces RI, it is the triggers. The index
is used to assist the searching that the RI triggers in the parent must do
to locate the matching children. (it is not "magic" - the RI trigger
contains an existence query similar to the one in your custom RI trigger...)

Our problem in Firebird is that we cannot choose the index that is used by
the RI constraint. If we implement a foreign key constraint, we are stuck
with that key-for-key index. When it is a good index, that is fine - we
don't want custom RI then. When it is a bad index, ideally, if we could
create our own index, the performance problem with our low-selectivity
lookup keys would go away and we would not need to write custom RI triggers.

> >If you need to apply custom RI triggers, be mindful of the possible
> >outcomes if you use transaction configurations that are at the "dirty" end
> >of the scale. ReadCommitted isolation, when carelessly used, allows the
> >possibility of phantom rows and undesirable overwriting. Declarative RI
> >-the FOREIGN KEY constraint - provides better protection than custom RI
> >triggers against the integrity breakages inherent in such conditions.
> >
> >
> >
>I only need the beforedelete check. The existence check will be done at
>app level so only allow insert via lookups to foreign tables.
>In the beforedelete can happen that trouble?

With deletes, no. But doing the existence check from the application is
not kosher. It could easily be out of date by the time you came to issue
the delete on the parent record.

I don't understand what you mean by "only allow insert via lookups to
foreign tables".

./heLen