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?
a1,a2should remain unmodified then they are no more foreign keys, so the constraint is no more valid. Or I am missing something?