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?

Answer 1

Ya temporarily disable the foreign key

set foreign_key_checks=0;

Answer 2

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.

Answer 3

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).

Answer 4

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.

Answer 5

You can temporarily disable foreign key constraints with this query:

SET foreign_key_checks = 0;

Answer 6

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).