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
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
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?