我有一个简单的MySQL数据库，在某些表列上有一些外键。一些外键列设置为ON DELETE SET
NULL，并且看起来工作正常。但是，对于其中一张表，我无法配置ON DELETE SET NULL-我只能配置ON DELETE CASCADE。

初始表如下所示：

CREATE TABLE sessions(
session_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL,
UNIQUE KEY uk_sessions(name))
COLLATE utf8_unicode_ci;

CREATE TABLE blocks(
block_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL,
UNIQUE KEY uk_blocks(name))
COLLATE utf8_unicode_ci;

CREATE TABLE edot(
edah_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL,
UNIQUE KEY uk_edot(name))
COLLATE utf8_unicode_ci;

CREATE TABLE campers(
camper_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
edah_id int,
FOREIGN KEY fk_edah_id(edah_id) REFERENCES edot(edah_id)
ON DELETE SET NULL
ON UPDATE CASCADE,
session_id int,
FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
ON DELETE SET NULL
ON UPDATE CASCADE,
first varchar(50) NOT NULL,
last varchar(50) NOT NULL,
email varchar(50) NOT NULL,
needs_first_choice bool DEFAULT 0,
active bool NOT NULL DEFAULT 1)
COLLATE utf8_unicode_ci;

以上所有工作正常。当我尝试这样做时，问题就来了：

CREATE TABLE block_instances(
block_id int NOT NULL,
FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
session_id int,
FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
ON DELETE SET NULL
ON UPDATE CASCADE,
PRIMARY KEY pk_block_instances(block_id, session_id))
COLLATE utf8_unicode_ci;

这返回

ERROR 1005 (HY000): Can't create table 'chugbot_db.block_instances' (errno: 150)

如果将第二个ON DELETE SET NULL更改为ON DELETE CASCADE，该错误消失：

CREATE TABLE block_instances(
block_id int NOT NULL,
FOREIGN KEY fk_block_id(block_id) REFERENCES blocks(block_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
session_id int,
FOREIGN KEY fk_session_id(session_id) REFERENCES sessions(session_id)
ON DELETE **CASCADE**
ON UPDATE CASCADE,
PRIMARY KEY pk_block_instances(block_id, session_id))
COLLATE utf8_unicode_ci;

工作正常。

谁能告诉我在这里的block_instances表中使用ON DELETE SET NULL有什么问题吗？似乎与campers表中的ON DELETE
SET NULL完全相同。