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