CODEWITHSUNDEEP
mysqlsqldatabase-design
user5377020
user5377020

Reputation:

Can't drop a MySQL table due to foreign key constraints

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?

Upvotes: 0

Views: 2310

Answers (1)

Dezza
Dezza

Reputation: 1094

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

Upvotes: 4

Related Questions