MySQL Bugs: #12449: DELETE from table references itself

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

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.

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

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.

This is a special case of 'deferred constraint checking. It is in the TODO.

Assigning Marko to improve the manual about this.

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."