Constraint does not exist unable to drop the table

Question

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  birth_day DATE,
  sex VARCHAR(1),
  salary INT,
  super_id INT,
  branch_id INT
);

CREATE TABLE branch (
  branch_id INT PRIMARY KEY,
  branch_name VARCHAR(40),
  mgr_id INT,
  mgr_start_date DATE,
  FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;

ALTER TABLE employee DROP CONSTRAINT branch_id;

Trying to Drop Constraint branch_id error says 'Constraint 'branch_id' does not exist.'

All I want to do is drop the employee table.

https://stackoverflow.com/questions/5094948/mysql-how-can-i-see-all-constraints-on-a-table (i.e. show create table or one of the information schema queries) should tell the correct name. — Allan Wind, Dec 28 '20 at 02:38

score 3 · Answer 1 · answered Dec 28 '20 at 02:49

3

First find the name of the constraint with

SHOW CREATE TABLE employee

The name isn't "branch_id".

answered Dec 28 '20 at 02:49

Gerard H. Pille

2,528
1
13
17

score 2 · Accepted Answer · answered Dec 28 '20 at 02:45

The actual name of your foreign key constraint is not branch_id, it is something else. The better approach here would be to name the constraint explicitly:

ALTER TABLE employee
ADD CONSTRAINT fk_branch_id FOREIGN KEY (branch_id)
REFERENCES branch(branch_id);

Then, delete it using the explicit constraint name you used above:

ALTER TABLE employee DROP FOREIGN KEY fk_branch_id;

Constraint does not exist unable to drop the table

2 Answers2