resolution order for foreign key actions?

From: Karl Czajkowski <karlcz(at)isi(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: resolution order for foreign key actions?
Date: 2016-11-08 20:08:47
Message-ID: 20161108200847.GA22642@moraine.isi.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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?

Thanks,

Karl

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Moreno Andreo 2016-11-08 20:13:52 Best practices to manage custom statistics
Previous Message Ryan Mahoney 2016-11-08 19:48:23 Running on Docker, AWS with Data Stored on EBS