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.

Solution 1

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.

Solution 2

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.

Comments

[AL90]

How would I go about do that? I have also tried: alter table EMPLOYEE drop Ssn_8;

[AL90]

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

[user710502]

do that in Department table first, drop the foreign key constraint of employee table there

[user710502]

Yes because you need to remove the foreign key constraint in the departments table first

[AL90]

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]

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

[user710502]

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

[AL90]

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;

[user710502]

Are there any constraints in the department table to employee? You have to check that there arent any other tables referencing Employee table.

[AL90]

I just updated my original post with the department table as well

[user710502]

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**

[AL90]

Thanks for sticking with my problem thus far. I have tried copying your code and running it, but I still get error 1064

[ypercubeᵀᴹ]

No, identifiers need to be quoted, only if they are reserved words.

[AL90]

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]

oh okay, Thank you for your help