Deleting a row with a self-referencing foreign key

Question

I have a MySQL table whose definition is as follows:

CREATE TABLE `guestbook` (
  `Id` int(10) unsigned NOT NULL,
  `ThreadId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `ThreadId` (`ThreadId`),
  CONSTRAINT `guestbook_ibfk_1` FOREIGN KEY (`ThreadId`) REFERENCES `guestbook` (`Id`)
) ENGINE=InnoDB;

and currently there's only 1 row in the table:

mysql> select * from guestbook;
+-----+----------+
| Id  | ThreadId |
+-----+----------+
| 211 |      211 |
+-----+----------+

The problem is that there's no way to delete this row without breaking the constraint.

mysql> delete from guestBook;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`polaris`.`guestbook`, CONSTRAINT `guestbook_ibfk_1` FOREIGN KEY (`ThreadId`) REFERENCES `guestbook` (`Id`))

As the ThreadId column was defined not null, it is also impossible to set the ThreadId to a different value temporarily to delete the row. Is there a way to delete the row without changing the definition of the table or dropping the entire table?

Wow that's just awesome, you've created a table structure that once it has rows you need to turn off constraints to delete the last row. Too bad the ID wasn't 22. — Conrad Frix, Jan 01 '11 at 08:41
@Conrad: Yeah, but this can always happen with any self-referencing foreign keys. :$ — K J, Jan 01 '11 at 08:58

score 26 · Accepted Answer · answered Jan 01 '11 at 08:20

26

You can temporarily disable foreign key constraints with this query:

SET foreign_key_checks = 0;

answered Jan 01 '11 at 08:20

Burak Guzel

1,267
1
12
12

Thanks, this seems to be the best way for now. – K J Jan 01 '11 at 08:56
Where should this query get run? On which table and how? You would better edit your answer and make it more clear. – Peyman Mohamadpour Nov 24 '16 at 18:24

score 8 · Answer 2 · answered May 12 '15 at 15:59

8

If you put an ON DELETE CASCADE action on your foreign key, you should be able to delete rows that are self-referencing.

CONSTRAINT `guestbook_ibfk_1` FOREIGN KEY (`ThreadId`) REFERENCES `guestbook` (`Id`) ON DELETE CASCADE

The benefit this has over using ON DELETE SET NULL is that you don't have to alter your schema to make the "ThreadId" column nullable.

answered May 12 '15 at 15:59

The Doge Prince

458
1
7
15

I do not understand why this answer not the best and correct answer. – RusAlex Jan 11 '17 at 10:39

score 7 · Answer 3 · answered Jan 01 '11 at 10:00

There are several workarounds. The approach suggested by others ...

SET foreign_key_checks = 0;

... will disable the foreign keys of every table. This is not suitable for use in a shared environment.

Another approach is to drop the foreign key using

ALTER TABLE `guestbook` 
    DROP FOREIGN KEY `guestbook_ibfk_1`
/

We can sort out the data using DML, and then reinstate the foreign key using:

ALTER TABLE `guestbook` 
    ADD CONSTRAINT `guestbook_ibfk_1` FOREIGN KEY (`ThreadId`) 
        REFERENCES `guestbook` (`Id`)
/

But is there a way to change the data without executing any DDL? Well, we can insert a new record and change the current record to reference it:

INSERT INTO `guestbook`  VALUES (212, 211)
/
UPDATE `guestbook` 
SET `ThreadId` = 212
WHERE `Id` = 211
/

Astute observers will have noticed that we have still ended up with a co-dependency, only between records. So we haven't really advanced; we now have two records we cannot delete, instead of one. (Incidentally this applies to whatever DML we might execute while the foreign key is dropped or disabled). So, perhaps we need to reconsider of the data model. Are we modelling a graph with circular dependencies or a hierarchy?

A hierarchical data structure needs at least one root node, a record on which other records can depend but which itself depends on no record. The usual way of implementing this is to make the foreign key column optional. At the toppermost level of the hierarchy the record must have a NULL in that column. Whether there should be only one such root node or whether several would be allowed is a matter for your business rules.

ALTER TABLE `guestbook` MODIFY `ThreadId`  int(10) unsigned
/

In modelling terms this is not different from a record which is its own master, but it is a more intuitive solution.

score 6 · Answer 4 · answered Oct 21 '11 at 09:36

6

The inability to delete a self-referencing row is a longstanding known bug/outstanding feature request in MySQL.

In many situations where you rub up against this problem you can NULL the foreign key before executing the delete, so your workaround affects only the rows you intend (uses the same WHERE clause).

answered Oct 21 '11 at 09:36

hawkip

144
1
3

1

Simplest solution in our use case. Thanks :) – Ranel Padon Oct 03 '19 at 20:24

score 0 · Answer 5 · answered Apr 01 '15 at 18:21

If you set an ON DELETE SET NULL on my Foreign Key, it let's me delete a self-referencing. If I don't specify an ON DELETE, MySQL defaults to RESTRICT.

Of course, make sure the column is NULLABLE. You may also try SET DEFAULT depending on what the default is. But remember NO ACTION is just an alias to RESTRICT in MySQL!

Only tested on MySQL 5.6 (which was not released when this question was originally posted).

XMen · Answer 6 · 2011-01-01T17:25:29.610

0

Ya temporarily disable the foreign key

set foreign_key_checks=0;

edited Jan 01 '11 at 17:25

answered Jan 01 '11 at 08:24

XMen

29,384
41
99
151

looks like it's foreign_key_checks, not foreign_key_constraints. :) – K J Jan 01 '11 at 08:57

Deleting a row with a self-referencing foreign key

6 Answers6

Linked

Related