Bug #7412 Cannot delete a row that references itself.
Submitted: 18 Dec 2004 19:29 Modified: 18 Dec 2004 23:08
Reporter: Sergey Koshcheyev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1.8 OS:Microsoft Windows (Windows XP SP2)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[18 Dec 2004 19:29] Sergey Koshcheyev
Description:
MySQL allows a row in an InnoDB table to reference itself, but it's not possible to delete such a row.
This is a bit contrived example, taken from the NHibernate test suite. This means it's probably not 'real-world', but I believe it's still a bug, since this works in Firebird for example (just checked), and should work in SQL Server too (unchecked, but NHibernate's test suite is supposed to run against it just fine).

How to repeat:
CREATE TABLE `tbl` (
  `id` int(11) NOT NULL auto_increment,
  `ref_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `ref_id` (`ref_id`),
  CONSTRAINT `ref_id` FOREIGN KEY (`ref_id`) REFERENCES `tbl` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO tbl VALUES (1, 1);
DELETE FROM tbl WHERE id = 1;

The DELETE will fail (ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails).

Suggested fix:
It should just go ahead and allow the deletion.
[18 Dec 2004 23:08] Heikki Tuuri
Hi!

I am changing this to a feature request. This is a special case where deferred constraints would be needed.

Workaround:

SET FOREIGN_KEY_CHECKS=0;

temporarily.

Regards,

Heikki
[30 Apr 2007 6:24] Bhavin Thakkar
Just write as below and it should solve the problem:

CONSTRAINT `ref_id` FOREIGN KEY (`ref_id`) REFERENCES `tbl` (`id`) ON DELETE CASCADE.

ON DELETE CASCADE WILL SOLVE YOUR PROBLEM.

Cheers,
Bhavin
[1 Oct 2009 22:53] Peter Gulutzan
Bug#15746 was marked as a duplicate of this one.
[19 Nov 2011 18:02] Andras Gyomrey
Any chance this feature will be added in a close future?