MySQL drop table

Question

I want to drop a table with drop table EMPLOYEE;

but I get the error: #1217 - Cannot delete or update a parent row: a foreign key constraint fails

I looked around on the internet to show the hidden constraints and found:

CREATE TABLE `EMPLOYEE` (
`Ssn` int(9) NOT NULL,
`Dno` int(11) NOT NULL,
UNIQUE KEY`Ssn_8` (`Ssn`),
UNIQUE KEY`Dno_13` (`Dno`),
CONSTRAINT `EMPLOYEE_ibfk_1` FOREIGN KEY(`Dno`) REFERENCES `DEPARTMENT` (`Dnumber`),
CONSTRAINT `EMPLOYEE_ibfk_2` FOREIGN KEY(`Dno`) REFERENCES `DEPARTMENT` (`Dnumber`),
CONSTRAINT `EMPLOYEE_ibfk_3` FOREIGN KEY(`Dno`) REFERENCES `EMPLOYEE` (`Dno`),
CONSTRAINT `EMPLOYEE_ibfk_4` FOREIGN KEY(`Dno`) REFERENCES `EMPLOYEE` (`Dno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1




   CREATE TABLE `DEPARTMENT` (
`Dnumber` int(11) NOT NULL,
`Mgr_ssn` int(9) NOT NULL,
UNIQUE KEY`Mgr_ssn` (`Mgr_ssn`),
UNIQUE KEY`Dnumber` (`Dnumber`),
CONSTRAINT `DEPARTMENT_ibfk_1` FOREIGN KEY(`Mgr_ssn`) REFERENCES `EMPLOYEE` (`Ssn`),
CONSTRAINT `DEPARTMENT_ibfk_2` FOREIGN KEY(`Mgr_ssn`) REFERENCES `EMPLOYEE` (`Ssn`),
CONSTRAINT `DEPARTMENT_ibfk_3` FOREIGN KEY(`Mgr_ssn`) REFERENCES `DEPARTMENT` (`Mgr_ssn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

After finding this I tried dropping the contraints first with: alter table EMPLOYEE drop contraint 'EMPLOYEE_ibfk_1';

but I keep getting: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''EMPLOYEE_ibfk_1'' at line 1

I have worked at dropping this table for several hours now and searched many topics on the internet. People ended up dropping the db, but I am unauthorized to drop db or create a db.

Looks like you misspelled 'constraint' in your ALTER...DROP statement, if you truly copied and pasted. — AJ., Apr 22 '12 at 22:08

ypercubeᵀᴹ · Accepted Answer · 2012-04-22T23:07:23.967

9

First remove the FOREIGN KEY constraints from DEPARTMENT to EMPLOYEE (note the weird syntax, you should use DROP FOREIGN KEY but with the constraint(!) identifier):

ALTER TABLE DEPARTMENT
  DROP  FOREIGN KEY  DEPARTMENT_ibfk_1 ,
  DROP  FOREIGN KEY  DEPARTMENT_ibfk_2 ;

Then drop the EMPLOYEE table:

DROP TABLE EMPLOYEE ;

The problem with your code was that you used DROP CONSTRAINT - which is correct, AFAIK it is the standard SQL syntax for ALTER TABLE.

MySQL however, "prefers" DROP FOREIGN KEY. In other words, it doesn't comply with standards in this case.

edited Apr 22 '12 at 23:07

answered Apr 22 '12 at 22:58

ypercubeᵀᴹ

113,259
19
174
235

Thanks for your help, your code allowed me to drop both tables. Could I ask what is the difference between this code and what I was doing before? Is it because it is dropping both keys in the same statement? – AL90 Apr 22 '12 at 23:03

score 1 · Answer 2 · answered Apr 22 '12 at 22:12

1

Foreign key names need to be quoted with backticks:

alter table EMPLOYEE drop constraint `EMPLOYEE_ibfk_1`;
                                     ^               ^

using ' single quotes turns that into just a string.

answered Apr 22 '12 at 22:12

Marc B

356,200
43
426
500

I still get the same error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint `EMPLOYEE_ibfk_1`' at line 1 – AL90 Apr 22 '12 at 22:15
Yes because you need to remove the foreign key constraint in the departments table first – user710502 Apr 22 '12 at 22:18
No, identifiers need to be quoted, only if they are reserved words. – ypercubeᵀᴹ Apr 22 '12 at 22:59

user710502 · Answer 3 · 2012-04-22T22:52:38.387

0

You need to remove the foreign keys in table Employee and also the foregin keys to table employee from Department and then try to drop it.

NOTE: Need to drop the foreign keys of the table being referenced first in the department table.

Also remove any references from Department to Employee in the Department table first.

edited Apr 22 '12 at 22:52

answered Apr 22 '12 at 22:08

user710502

11,181
29
106
161

How would I go about do that? I have also tried: alter table EMPLOYEE drop `Ssn_8`; – AL90 Apr 22 '12 at 22:12
do that in Department table first, drop the foreign key constraint of employee table there – user710502 Apr 22 '12 at 22:17
I just tried that on the Department table as well, but get the same error. Am i suppose to try and drop the constraint or the actually key? – AL90 Apr 22 '12 at 22:20
I get error: #1025 - Error on rename of './leal@002ddb/#sql-392_7d0fe' to './leal@002ddb/DEPARTMENT' (errno: 150) when dropping the key itself and get error #1064 when i try and drop the constraint – AL90 Apr 22 '12 at 22:21
Ok do a few things here, I see you are referencing foreign keys within the same table EMPLOYEE_ibfk_3 and EMPLOYEE_ibfk_4. Remove those first, then the department ones ** Dont drop the key.. there is no need, just the contraints – user710502 Apr 22 '12 at 22:23
Ok, I just tried removing EMPLOYEE_ibfk_3 and EMPLOYEE_ibfk_4 as well as the two from Departments table, but i keep getting error 1064, this is what i typed in: alter table EMPLOYEE drop constraint `EMPLOYEE_ibfk_3`; – AL90 Apr 22 '12 at 22:30
Are there any constraints in the department table to employee? You have to check that there arent any other tables referencing Employee table. – user710502 Apr 22 '12 at 22:31
I just updated my original post with the department table as well – AL90 Apr 22 '12 at 22:34
OK TRY THIS, for both tables alter the tables to do this FOREIGN KEY (blah) REFERENCES BLAH(BLAh) ON DELETE CASCADE "NOTICE THE ON DELETE CASCADE" ......Then try to drop the constraints** – user710502 Apr 22 '12 at 22:39
Thanks for sticking with my problem thus far. I have tried copying your code and running it, but I still get error 1064 – AL90 Apr 22 '12 at 22:53

MySQL drop table

3 Answers3