CODEWITHSUNDEEP
mysqlforeign-keyscascading-deletes
coulttac
coulttac

Reputation: 19

Foreign Key Constraint On delete cascade not effective

I have created two tables (emp and dept). Emp contains a foreign key (deptid). I am attempting to delete a row from the dept table and am receiving a foreign key constraint error. I then altered the emp table foreign key to add constraint and delete on cascade. The code is copied here

create table dept (
  deptid int primary key,
  deptname varchar(20),
  locid int);

create table emp (
  empid int primary key,
  frname varchar(15),
  lname varchar(20),
  hiredate datetime,
  deptid int,
  foreign key (deptid) references dept(deptid));

alter table emp add constraint fk_deptid foreign key (deptid) 
    references dept(deptid) on delete cascade;

This is the error message that I receive:

Query Error: Error: ER_ROW_IS_REFERENCED_2: Cannot delete or update a parent row: a foreign key constraint fails (test.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (deptid) REFERENCES dept (deptid))

Upvotes: 1

Views: 2140

Answers (1)

The Impaler
The Impaler

Reputation: 48875

The problem is that you are creating two foreign key constraints for the same column. Avoid doing this, it's confusing.

  • The first one is created while you create the table and is unnamed. Since you didn't provide a name for it, MySQL automatically names it for you as emp_ibfk_1. This one does not have CASCADE DELETE and prevents deletion of parent keys.

  • The second one is on a separate SQL statement and you name it fk_deptid. This one has CASCADE DELETE and allows deletion of parent keys.

While enforcing the foreign key constraints MySQL cannot delete the row since the first constraint does not allow it. That's the error you are getting.

This is a confusing situation and I would avoid doing something like this. I would suggest having a single FK constraint for the column; specifically I would remove the first one.

For example:

create table dept (
  deptid int primary key,
  deptname varchar(20),
  locid int);

create table emp (
  empid int primary key,
  frname varchar(15),
  lname varchar(20),
  hiredate datetime,
  deptid int
  -- , foreign key (deptid) references dept(deptid) -- removed
);

alter table emp add constraint fk_deptid foreign key (deptid) 
    references dept(deptid) on delete cascade;

insert into dept (deptid, deptname, locid) 
  values (1, 'Math', 123);    

insert into emp (empid, frname, lname, hiredate, deptid) 
  values (10, 'Peter', 'Fonda', null, 1);

delete from dept where deptid = 1; -- works!

Upvotes: 1

Related Questions