MySQL Bugs: #4042: Imposible to delete self-referenced record

Bug #4042	Imposible to delete self-referenced record
Submitted:	7 Jun 2004 17:55	Modified:	11 Aug 2005 14:32
Reporter:	Ruslan Zakirov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.0.20	OS:
Assigned to:		CPU Architecture:	Any

Description:
It's impossible to delete row when FK field is not NULL and references on itself.

How to repeat:
CREATE TABLE `fk_test` (
  `id` int(11) NOT NULL auto_increment,
  `effective_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_fk_test1` (`effective_id`),
  CONSTRAINT `fk_test_ibfk_1` FOREIGN KEY (`effective_id`) REFERENCES `fk_test` (`id`)
) TYPE=InnoDB;

mysql> insert into fk_test values(10, 10);
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM fk_test where id = 10;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails

Suggested fix:
MySQL should delete record if parent and child is the same.

Hi!

This is a known problem.

You can use

SET FOREIGN_KEY_CHECKS=0;
DELETE ...;
SET FOREIGN_KEY_CHECKS=1;

as a workaround.

Regards,

Heikki

Duplicate of bug #12449.