mysql设置on set null_mysql – 多列外键：将单列设置为Null“ON D...

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

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

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

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