由于外键限制，无法删除MySQL表 [英] Can't drop a MySQL table due to foreign key constraints

问题描述

我有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 projs 中的$ c>变为 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

这篇关于由于外键限制，无法删除MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！