MySQL Bugs: #38585: Foreign key constraint for column in same table fails on delete when same row

Bug #38585	Foreign key constraint for column in same table fails on delete when same row
Submitted:	5 Aug 2008 22:01	Modified:	16 Oct 2008 3:48
Reporter:	Tim Faust	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.0.5 alpha	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key

Description:
If you have a table with a foreign key constraint from one column to another, the row can't be deleted if the row refers to itself.

I realize I can work around this (partly) by changing the field to DEFAULT NULL and the constraint to SET NULL, or to not add a row that refers to itself, but I would like to use the same schema across multiple DBMS platforms. 

How to repeat:
Make a table:
DROP TABLE IF EXISTS `tbl_activity`;
CREATE TABLE `tbl_activity` (
  `ACTIVITYID` int NOT NULL,
  `MASTERACTIVITYID` int NOT NULL,
   PRIMARY KEY (`ACTIVITYID`),
  CONSTRAINT `ACTIVITY_MASTERACTIVITYID_FK` FOREIGN KEY (`MASTERACTIVITYID`) REFERENCES `tbl_activity` (`ACTIVITYID`)
   ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add a row:
insert into tbl_activity values(1,1)

Try to delete the row:
delete from tbl_activity where activityid = 1;

You should get the following error:
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`tbl_activity`, CONSTRAINT `ACTIVITY_MASTERACTIVITYID_FK` FOREIGN KEY (`MASTERACTIVITYID`) REFERENCES `tbl_activity` (`ACTIVITYID`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Suggested fix:
The biggest problem I have is that Microsoft SQL Server ignores the constraint if it is the same row and MySQL doesn't.   

I realize that "make it like MSSQL" isn't a really good suggestion, but it makes more sense to me than what happens now. It seems that on a DELETE, a row that only has a constraint issue with itself shouldn't be blocked from deleting.

This is a documented limitation. Read http://dev.mysql.com/doc/refman/6.0/en/innodb-foreign-key-constraints.html:

"Deviation from SQL standards: 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 deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself via a foreign key."

Thansk, I missed that in the documentation. I'll implement my work around. Feel free to close if you prefer that to "To be fixed later"