Re: resolution order for foreign key actions?

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?

>
> Is there a definitive way to introspect the informatation_schema or
> pg_catalog to determine which behaviors will effectively apply to a
> given "DELETE FROM ..." or "UPDATE ..." statement?

Just to clear you are talking about FK constraints, correct?

AFAIK they are just a form of trigger and the rules they follow can be
found here:

https://www.postgresql.org/docs/9.5/static/sql-createtrigger.html

"If multiple triggers of the same kind are defined for the same event,
they will be fired in alphabetical order by name."

There is more, so I would read through the whole thing.


>
> Thanks,
>
>
> Karl
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com