多列外键：将单列设置为空“ON DELETE”而不是全部 [英] Multiple Column Foreign Key: Set single column to Null "ON DELETE" instead of all

问题描述

要求：如果从父列中删除一行应该设置为NULL，而不是外键中的两列。

示例/说明：可能会列出学生参加讲座，也可选择参加讲座小组讨论。如果讲座被删除，则应删除所有学生名单（Works）及其所有组（Works）。如果只有一个组被删除，那么学生仍然应该被列入讲座，但不应该再被分配到一个组（问题）。

示例/ SQL ：以下SQL说明了这个示例，但最后一条语句不起作用，因为最后一个FOREIGN KEY要求lectureId和groupId都为NULLable，但是使两个NULLable都意味着删除一个组

  CREATE TABLE lectures（
 lectureId INT NOT NULL，
 title VARCHAR （10）NOT NULL，
 PRIMARY KEY（lectureId）
）; 
 
 CREATE TABLE组（
 lectureId INT NOT NULL，
 groupNo INT NOT NULL，
 title VARCHAR（10）NOT NULL，
 PRIMARY KEY（lectureId ，groupNo），
 FOREIGN KEY（lectureId）参考讲座（lectureId）
在更新级联上删除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 
）; 
  

解决方案

最好的解决方案似乎是使用外键和触发器的组合。

这个问题可以通过下面的语句来解决：

  lectureId INT NOT NULL，
 title VARCHAR（10）NOT NULL，
 PRIMARY KEY（lectureId）
）; 
 
 CREATE TABLE组（
 lectureId INT NOT NULL，
 groupNo INT NOT NULL，
 title VARCHAR（10）NOT NULL，
 PRIMARY KEY（lectureId ，groupNo），
 FOREIGN KEY（lectureId）参考讲座（lectureId）
在更新级联上删除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; 
  

请注意，最后一个外键的ON DELETE CASCADE不会导致级联删除触发器已经通过删除相应的行来删除外键引用。

另外：除了使用ON DELETE CASCADE之外，还可以使用ON DELETE SET NULL，但是lectureId必须是可空的，其中一个应该包含一个CHECK（lectureId IS NOT NULL），以确保它永远不会被设置为空。

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
 );

解决方案

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

这篇关于多列外键：将单列设置为空“ON DELETE”而不是全部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！