mysql - Multiple Column Foreign Key: Set single column to Null "ON DELETE" instead of all

Question

General: Given a foreign key over several columns, some of them might be NULL.
By default (MATCH SIMPLE) MySQL/MariaDB InnoDB does not check the foreign key as long as at least one column of a multi column foreign key is NULL.

Requirement: If a row is deleted from the parent one column of the corresponding child should be set to NULL, but not both columns in the foreign key.

Example/Description: A student might be listed for a lecture, and optionally for one of the lectures groups as well. If the lecture is deleted all student listing should be removed (Works) and all its groups (Works). If only a single group is deleted, then the students should still be listed for the lecture, but they should not be assigned to a group any more (Problem).

Example/SQL: The following SQL illustrates this example, but the last statement will not work, as the last FOREIGN KEY requires both lectureId and groupId to be NULLable, but making both NULLable will imply that deleting a group will also set the lectureId to NULL.

CREATE TABLE lectures (
  lectureId INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId)
 );

CREATE TABLE groups (
  lectureId INT NOT NULL,
  groupNo INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId,groupNo),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

CREATE TABLE studentListed (
  studentId INT NOT NULL,
  lectureId INT NOT NULL,
  groupNo INT NULL,
  PRIMARY KEY (studentId,lectureId),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (lectureId,groupNo) REFERENCES groups (lectureId,groupNo)
    ON UPDATE CASCADE ON DELETE SET NULL
 );

Answer 1

After some research it seems like that particular requirement is not implementable using foreign keys.

The best solution seems to be using a mix of Foreign Keys and a Trigger.

The problem can be solved for the given example by the following statements:

CREATE TABLE lectures (
  lectureId INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId)
 );

CREATE TABLE groups (
  lectureId INT NOT NULL,
  groupNo INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId,groupNo),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

CREATE TABLE studentListed (
  studentId INT NOT NULL,
  lectureId INT NOT NULL,
  groupNo INT NULL,
  PRIMARY KEY (studentId,lectureId),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (lectureId,groupNo) REFERENCES groups (lectureId,groupNo)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

CREATE TRIGGER GroupDelete BEFORE DELETE ON groups
FOR EACH ROW
  UPDATE studentListed SET studentListed.groupNo = NULL
    WHERE studentListed.lectureId = OLD.lectureId
    AND studentListed.groupNo = OLD.groupNo;

Note that the "ON DELETE CASCADE" of the last foreign key will never lead to a cascaded delete as the Trigger already removed the foreign key references by null-ing the corresponding rows.

Addition: Instead of using "ON DELETE CASCADE" one could use "ON DELETE SET NULL" with the same trigger, but then "lectureId" has to be nullable and one should include a "CHECK (lectureId IS NOT NULL)" to ensure it is never set to null