mysql+多列外键,mysql – 多列外键：将单列设置为Null“ON DELETE”而不是全部

概述：给定多个列的外键,其中一些可能为NULL.

默认情况下(MATCH SIMPLE)只要多列外键的至少一列为NULL,MySQL / MariaDB InnoDB就不会检查外键.

要求：如果从父对象中删除了一行,则应将相应子对象的一列设置为NULL,而不是外键中的两列.

示例/说明：学生可能会被列为讲座,也可能是其中一个讲座组.如果讲座被删除,则应删除所有学生列表(Works)及其所有组(Works).如果只删除了一个组,那么学生仍应列入讲座,但不应再将他们分配到一个组(问题).

示例/ SQL：以下SQL说明了此示例,但最后一个语句不起作用,因为最后一个FOREIGN KEY要求讲义和groupId都是NULLable,但同时使两者都为NULL意味着删除一个组也会将lectureId设置为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

);

解决方法:

经过一些研究后,似乎特定要求无法使用外键实现.

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

通过以下语句可以解决给定示例的问题：

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”将永远不会导致级联删除,因为Trigger已通过使相应行归零来删除外键引用.

增加：不使用“ON DELETE CASCADE”,而是可以使用相同触发器的“ON DELETE SET NULL”,但是“lectureId”必须是可空的,并且应该包含“CHECK(讲话不是空)”以确保它永远不会设置为null

标签：sql,mysql,foreign-keys,mariadb,innodb

来源： https://codeday.me/bug/20191008/1872912.html