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

You can temporarily disable foreign key constraints with this query:

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.