Bug #12449 DELETE from table references itself
Submitted: 9 Aug 2005 4:37 Modified: 16 Sep 2005 13:21
Reporter: Vadim Tkachenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:all OS:Any (all)
Assigned to: Marko Mäkelä CPU Architecture:Any

[9 Aug 2005 4:37] Vadim Tkachenko
Description:
CREATE TABLE Employee
(
     empId                    INT          NOT NULL,
     lastName               VARCHAR(32) NULL,
     firstName              VARCHAR(32) NULL,
     mgrId                     INT         NULL,
     PRIMARY KEY (empId),
     FOREIGN KEY (mgrId) REFERENCES Employee (empId)
) type=InnoDB;

insert into Employee values (1, 'lastEngOne', 'firstEngOne', 1);
delete from Employee where empId = 1;

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key
constraint fails

But MySQL doc says:
Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY  constraints row-by-row. According to the SQL standard, the default behavior should be that constraints are only checked after the WHOLE SQL statement has been processed.

How to repeat:
Try script.
[9 Aug 2005 5:55] Heikki Tuuri
Vadim,

'row-by-row' in this case means that BEFORE deleting the row, InnoDB checks if it is referenced. It is referenced by itself -> a foreign key error.

I will document this more clearly in the manual.

Workaround: SET FOREIGN_KEY_CHECKS=0 during the delete.

Regards,

Heikki
[10 Aug 2005 19:31] Peter Zaitsev
Heikki, 

What does standard says about this case ? 

The customer complaint was the behavior is different from what over databases have.

Also from common sense it looks like a bug to me.  I can understand it might be to hard to fix right now but if it is a bug it should not be dismissed but fixed in the future.
[11 Aug 2005 15:11] Heikki Tuuri
This is a special case of 'deferred constraint checking. It is in the TODO.
[4 Sep 2005 22:08] Heikki Tuuri
Assigning Marko to improve the manual about this.
[16 Sep 2005 13:21] Marko Mäkelä
I have tried to address this in the documentation,
<http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html>:

"Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself via a foreign key."