Postgres foreign key with ON DELETE SET NULL for only some of its columns

Question

I have added a (new) 2nd foreign key to a table a. Its 1st foreign key is optional, hence the constraint ON DELETE SET NULL on the foreign key. The 2nd foreign key is mandatory, hence the (new) constraints NOT NULL on a1 and a2 and (new) the constraint ON DELETE CASCADE on the foreign key. The 2nd foreign key also consists of a subset of the columns for the 1st foreign key.

CREATE TABLE a
(
  a0 INTEGER NOT NULL PRIMARY KEY,
  -- other columns
  a1 INT NOT NULL,
  a2 INT NOT NULL,
  a3 INT,
  a4 INT,
  FOREIGN KEY (a1, a2)         REFERENCES b ON DELETE CASCADE,
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES c ON DELETE SET NULL
);

The problem that I run into is that I can now no longer DELETE FROM c, since this invalidates the NOT NULL constraints on a1 and a2. What I would like to happen in the case of such deletes is for a1, a2 to remain unmodified and for a3, a4 to be set to NULL. Is there a concise syntax (more concise than with an extra DELETE trigger) for defining a constraint that achieves this?

Answer 1

Currently there is no way for the CASCADE action to set only some of the columns to NULL. So the only options I see would be:

• have all the 4 columns defined as NULL and use ON DELETE SET NULL action. Seems like this is not an option in your case.

• add another table to hold this relationship (where all the columns are not nullable) and use ON DELETE CASCADE.

• a trigger.

• create a procedure (function in Postgres) to handle deletions of table c. The function would first modify the columns (c,d) in table a and then delete from table c. Then you have to make sure that all users/applications use this function and do not directly delete from table c.