删除具有自引用外键的行

I have a MySQL table whose definition is as follows:

我有一个MySQL表，它的定义如下:

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?

由于ThreadId列定义为not null，因此也不可能将ThreadId设置为临时的不同值来删除行。是否有一种方法可以在不更改表的定义或删除整个表的情况下删除行?

6 个解决方案

#1

You can temporarily disable foreign key constraints with this query:

您可以使用此查询暂时禁用外键约束:

SET foreign_key_checks = 0;

#2

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:

我们可以使用DML对数据进行排序，然后使用:

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:

但是有没有一种方法可以在不执行任何DDL的情况下更改数据呢?那么，我们可以插入一个新的记录，并更改当前记录以引用它:

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?

敏锐的观察者会注意到，我们最终仍然是相互依赖的，只是记录之间的相互依赖。我们还没有深入;我们现在有两个不能删除的记录，而不是一个。(顺便说一句，这适用于在删除或禁用外键时可能执行的任何DML)。因此，也许我们需要重新考虑数据模型。我们是用循环依赖关系还是层次结构来建模一个图?

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.

分层数据结构至少需要一个根节点，一个记录可以依赖于其他记录，但它本身不依赖于记录。通常的实现方法是使外键列可选。在层次结构的顶层，记录必须在该列中有一个NULL。是否应该只有一个这样的根节点，或者是否允许有几个这样的节点，这关系到您的业务规则。

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.

就建模而言，这与记录本身并无不同，但它是一种更直观的解决方案。

#3

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.

与使用ON DELETE SET NULL相比，它的好处是，您不必修改您的模式，使“ThreadId”列成为空。

#4

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

无法删除自引用行是MySQL中长期存在的已知bug/未解决的特性请求。

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

在许多情况下，当您遇到这个问题时，您可以在执行delete之前使外键无效，因此您的工作区只影响您想要的行(使用相同的where子句)。

#5

Ya temporarily disable the foreign key

可以暂时禁用外键

set foreign_key_checks=0;

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

如果您在我的外键上设置ON DELETE set NULL，那么让我删除一个自引用。如果我没有指定ON DELETE, MySQL默认会进行限制。

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!

当然，确保列是空的。您还可以尝试设置默认值，这取决于默认值是什么。但是记住，没有动作只是MySQL中限制的别名!

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

只在MySQL 5.6上测试过(这个问题最初发布时没有发布)。

#1