Deleting a row with a self-referencing foreign key

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.

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.

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

You can temporarily disable foreign key constraints with this query:

SET foreign_key_checks = 0;