Re: deferred cascade delete re-check at end of transaction?

On Mon, 14 Oct 2002, Michael Adler wrote:

>
> I have written a test that demonstrates a behavior that surprises me.
>
> I store a foreign key with a deferable cascade-delete. While in a
> transaction, I delete and then re-insert the referenced key. Since the key
> value is back in the table, I would expect the delete to NOT cascade, but
> apparently it does.

We've not been able to determine what the "correct" behavior for this is,
the spec is not entirely clear.

The question is what are matching rows and does re-inserting a row that
matches that key value invalidate the mark for deletion, because the SQL92
says basically (11.8):
5) If a <delete rule> is specified and a row of the referenced
table that has not previously been marked for deletion is marked
for deletion, then

Case:

a) If <match type> is not specified or if FULL is specified,
then

Case:

i) If the <delete rule> specifies CASCADE, then all matching
rows are marked for deletion.

It talks about matching rows and the time those are chosen, but we've
never managed a real consensus on what the spec intends. We've had
a couple of arguments about it in the past on -hackers.

> Is there another way to acheive this behavior?
Apart from making a new trigger, I don't think so currently. It's
not too hard to add a check to a copy of the trigger (new versions of no
action already do), but then you're not really using the foreign key
stuff, per se.