7

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?

3
  • If a1, a2 should remain unmodified then they are no more foreign keys, so the constraint is no more valid. Or I am missing something?
    – Renzo
    Aug 28, 2016 at 7:43
  • What is the primary key of the table? Aug 28, 2016 at 8:41
  • @ypercubeᵀᴹ I've added the table's primary key.
    – Drux
    Aug 28, 2016 at 10:49

1 Answer 1

6

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.

1
  • I'll probably go with a trigger then.
    – Drux
    Aug 28, 2016 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.