Bug #10331 foreign key constraint blocks delete on self reference

• Submitted: 3 May 2005 13:20
• Modified: 11 Aug 2005 14:27
• Reporter: Armin Haaf
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 4.1.10
• OS: Linux (Linux)
• CPU Architecture: Any

[3 May 2005 13:20] Armin Haaf

Description:
A delete with a self reference constraint fails. Just look at how to repeat

How to repeat:
drop table test;

create table test (
  _id bigint(20) NOT NULL,
  rootId bigint(20),
  PRIMARY KEY  (_id),
  CONSTRAINT `FKRoot` FOREIGN KEY (rootId) REFERENCES test(_id)
);

insert into test values (1,1);

delete from test where _id=1;

-> a foreign key constraint fails

[3 May 2005 13:45] Heikki Tuuri

Deferred constraint checking is in the TODO.

You can work around with

SET FOREIGN_KEY_CHECKS=0;

<delete>

Set FOREIGN_KEY_CHECKS=1;

Regards,

Heikki

[11 Aug 2005 14:27] Heikki Tuuri

Duplicate of bug #12449.