MySQL拒绝使用ON DELETE SET NULL而不是ON DELETE CASCADE的列 [英] MySQL rejects column with ON DELETE SET NULL but not ON DELETE CASCADE

问题描述

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

I have a simple MySQL database with some foreign keys on certain table columns. Some of the foreign key columns are set to ON DELETE SET NULL, and it seems to work fine. However, for one of the tables, I can't configure ON DELETE SET NULL - I can only configure 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;

以上所有方法都很好.当我尝试执行此操作时就会出现问题:

The above all works fine. The problem comes when I try to do this:

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，错误将消失:

The error goes away if I change the second ON DELETE SET NULL to 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完全相同.

Can anyone tell me what is wrong with using ON DELETE SET NULL in the block_instances table here? It seems exactly the same as the ON DELETE SET NULL in the campers table.

推荐答案

事实证明这是MySQL中的错误:

This turned out to be a bug in MySQL:

http://bugs.mysql.com/bug.php?id=80052

这篇关于MySQL拒绝使用ON DELETE SET NULL而不是ON DELETE CASCADE的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！