由于外键限制，无法删除MySQL表

本文介绍了由于外键限制，无法删除MySQL表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有2个使用

CREATE TABLE projs ( id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, compname VARCHAR (200), title VARCHAR (200), imageurl VARCHAR(300), sumsmall VARCHAR (250), sumfull VARCHAR (5000), results VARCHAR (2000), postdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP, caseid MEDIUMINT NULL, hide TINYINT NOT NULL, carid MEDIUMINT, FOREIGN KEY (caseid) REFERENCES cases(id) ON DELETE SET NULL, FOREIGN KEY (carid) REFERENCES work_carousels(id) ON DELETE SET NULL )

和

CREATE TABLE cases ( id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, compname VARCHAR (200), sumsmall VARCHAR (500), situation VARCHAR (1000), imageurl VARCHAR(300), approach VARCHAR (1000), deliverables VARCHAR (1000), results VARCHAR (1000), conclusion VARCHAR (1000), postdate DATETIME DEFAULT CURRENT_TIMESTAMP, carid MEDIUMINT, FOREIGN KEY (carid) REFERENCES work_carousels(id) ON DELETE SET NULL )

重要的部分是 projs 中有一个列引用了例中的列。我收到错误消息：

the important part being that there is a column in projs that references a column in cases. I get the error:

＃1217-无法删除或更新父行：外键约束失败

#1217 - Cannot delete or update a parent row: a foreign key constraint fails

当我尝试

DROP TABLE cases;

这似乎很奇怪，因为我已经配置了列 caseid 变为 NULL 。我试图将这些值手动设置为 NULL ：

which seems weird because I've configured the column caseid in projs to become NULL if what it's referencing gets deleted. I tried to manually set those values to NULL:

UPDATE projs SET caseid=NULL; DROP TABLE cases;

，但出现相同的错误。

but got the same error.

知道我在做什么错吗？

推荐答案

您有一个约束（外键）正在引用要删除的表。删除表时，约束将毫无意义，因为它引用了无法解析的对象。

You have a constraint (The foreign key) that is referencing a table that is to be deleted. When the table is dropped the constraint won't make any sense because it references an object that can't be resolved.

首先，您需要在 projs 引用个案例，然后可以删除个案例。

First you will need to drop the constraint on projs that references cases, and then you can drop cases.

ON DELETE SET NULL 或您配置为让您说的任何内容

The ON DELETE SET NULL or whatever you configured to make you say

我已经将projs中的caseid列配置为如果引用的内容被删除，则为NULL

I've configured the column caseid in projs to become NULL if what it's referencing gets deleted

仅适用于删除例的记录，而不适用于整个表。

Only applies to records in cases being deleted, not the whole table.

删除外部键，您需要使用 ALTER TABLE projs DROP FOREIGN KEY [keyname] 。您需要将 [keyname] 替换为可以从 SHOW CREATE TABLE projs

To remove the foreign key you need to use ALTER TABLE projs DROP FOREIGN KEY [keyname]. You will need to replace [keyname] with the name of the key that you can obtain from SHOW CREATE TABLE projs