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

Bug #7412	Cannot delete a row that references itself.
Submitted:	18 Dec 2004 20:29	Modified:	19 Dec 2004 0:08
Reporter:	Sergey Koshcheyev
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	4.1.8	OS:	Microsoft Windows (Windows XP SP2)
Assigned to:	Heikki Tuuri	Target Version:
Triage:	Triaged: D5 (Feature request)

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.