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

[7 Jun 2004 17:55] Ruslan Zakirov
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.
[7 Jun 2004 17:58] Heikki Tuuri
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
[11 Aug 2005 14:32] Heikki Tuuri
Duplicate of bug #12449.