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

I have 2 tables created with

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

)

and

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

)

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

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

when I try to

DROP TABLE cases;

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.

Any idea what I'm doing wrong?

解决方案

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.

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

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

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.

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