Bug #4042 Imposible to delete self-referenced record

• Submitted: 7 Jun 2004 19:55
• Modified: 11 Aug 2005 16:32
• Reporter: Ruslan Zakirov
• Status: Duplicate
• Category: Server: InnoDB
• Severity: S3 (Non-critical)
• Version: 4.0.20

[7 Jun 2004 19: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 19: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 16:32] Heikki Tuuri

Duplicate of bug #12449.