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

问题描述

常规:给定多个列的外键，其中一些可能为 NULL.
默认情况下 (MATCH SIMPLE) MySQL/MariaDB InnoDB 只要多列外键的至少一列为 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.

要求:如果从父项中删除一行，则对应子项的一列应设置为 NULL，但不能将外键中的两列都设置为 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).

示例/SQL:下面的 SQL 说明了这个示例，但最后一条语句不起作用，因为最后一个 FOREIGN KEY 要求 LectureId 和 groupId 都可以为 NULL，但是使两者都为 NULL 意味着删除组也会将 LectureId 设置为 NULL.

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;

请注意，最后一个外键的ON DELETE CASCADE"永远不会导致级联删除，因为触发器已经通过将相应行设为空来删除外键引用.

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.

补充:除了使用ON DELETE CASCADE"，可以使用ON DELETE SET NULL"和相同的触发器，但是lectureId"必须可以为空，并且应该包含CHECK(lectureId IS NOTNULL)" 以确保它永远不会设置为 null

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