Re: resolution order for foreign key actions?

On Tue, Nov 8, 2016 at 1:20 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 11/08/2016 12:08 PM, Karl Czajkowski wrote:
>
>> Hi,
>>
>> Is there a formal definition for the order in which constraint actions
>> (i.e. the ON DELETE or ON UPDATE rules) are applied when there are
>> multiple overlapping/relevant constraints?
>>
>> I have struggled to find an answer in the manual, but my experiments
>> suggest that they are interpreted in the order in which the
>> constraints were defined and the first rule in this order is applied
>> while subsequent rules are ignored. This can be very confusing if one
>> rule says CASCADE and another NO ACTION, and you need to understand
>> this order of definition to know whether a delete will cascade or
>> raise an error.
>>
>
> Can you provide an example?
>
>
​Karl,​

​Yes, please, but...

ON DELETE starts with the "one" side of a one-to-many relationship​. When
deleting the one row the question is what should be done with the many rows
which refer to it. If ALL of the many rows agree to be deleted then the
one row in question can go away and no error is raised. If ANY of the many
rows refuse to die then the one row in question must remain in order to
maintain referential integrity - thus an error will be raised.

​ANY/ALL logic generally shouldn't depend on the order in which the
triggers fire though I suppose you could possible setups a convoluted
series of FKs that would cause it to do so. Its hard to imagine one so if
you have a ready example that would help.

Note, I'm going off of logic here - hopefully the SQL Standards Committee
hasn't gotten to deeply involved in this area :)

David J.​