At 09:17 AM 12/7/2004, Valentin Samko wrote:

>Thanks for the info, this explains everything. Still, this behaviour
>seems unnatural to me, wouldn't it be more logical to delete the child
>records before deleting the parent one, i.e. avoiding the inconsistent
>state where we have a child with parent id, with no such parent?

Umnnn now that I think about it, that is interesting. The fact
of the implementation is that the cascade is implemented as an
after-delete system trigger on the parent table. That seems to
be an arbitrary decision - it could have been a before delete
system trigger, I guess, and that would have the behavior you
expect.

So, I went to look at the standard and see what it has to say
about the visibility of the referenced row from a before delete
trigger in the referencing table during the execution of an
ON DELETE CASCADE trigger. The answer is long, somewhat obscure,
and really comes down to this note:

NOTE 398 — “Marking for deletion” is an implementation-dependent mechanism.

So, yes, there is a temporary state during the execution of a
foreign key constraint - ON UPDATE or ON DELETE - when the parent
is changed and the child is not. That condition can be detected
by triggers on the child. As far as I understand the standard,
our behavior is legitimate, if weird.

Out of curiosity, why is this important to you?


Regards,


Ann