CODEWITHSUNDEEP
mysql
david_m
david_m

Reputation: 3

Cant drop table because of a foreign key, but also cant drop foreign key

I'm trying to drop 2 tables -

  1. drop table employee; that has one fk to itself - named 'super_id', and one to 'branch' table - named 'branch_id'
  2. drop table branch; that has one fk to 'employee' table - 'mgr_id'

because they have referenced foreign keys between them, I get the error massage: Cannot drop table 'employee' referenced by a foreign key constraint 'branch_ibfk_1' on table 'branch'/// and visa versa

so I tried dropping the constraints of the foreign keys between the two tables: ALTER TABLE employee DROP FOREIGN KEY super_id; ALTER TABLE employee DROP FOREIGN KEY branch_id; ALTER TABLE branch DROP FOREIGN KEY mgr_id;

but I also got an error, for the employee table: "Can't DROP 'super_id' / 'branch_id'; check that column/key exists"

and for the branch table: "Can't DROP 'mgr_id'; check that column/key exists"

I know I can erase the DB, but I want to know how to work correctly. can anyone tell what am I doing wrong? TNX

Upvotes: 0

Views: 2763

Answers (2)

Fanta Shakur
Fanta Shakur

Reputation: 11

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

fk_symbol = If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint looks something like this = employee_ibfk_1 on table employee

ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1;

Upvotes: 1

Divin Irakiza
Divin Irakiza

Reputation: 357

Try this on both tables

SET FOREIGN_KEY_CHECKS=0; DROP TABLE employees; SET FOREIGN_KEY_CHECKS=1;
SET FOREIGN_KEY_CHECKS=0; DROP TABLE branches; SET FOREIGN_KEY_CHECKS=1;

Upvotes: 2

Related Questions