MySQL Bugs: #7412: Cannot delete a row that references itself.

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:	Windows (Windows XP SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any

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.

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

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

Bug#15746 was marked as a duplicate of this one.

Any chance this feature will be added in a close future?