删除一个具有自引用外键的行

Question

我有一个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;

目前该表中只有一行数据：

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

问题是没有办法删除这行数据而不违反约束条件。

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

由于ThreadId列被定义为非空，因此无法将ThreadId临时设置为其他值以删除行。有没有办法在不更改表定义或删除整个表的情况下删除该行数据？

- K J

---

一行作为其自身的子行是很奇怪的。 - Chandu

哇，这太棒了！你创建了一个表结构，一旦有行数据，就需要关闭约束才能删除最后一行。只可惜它的ID不是22。 - Conrad Frix

@Conrad：是的，但这种情况在任何自引用外键中都可能发生。:$ - K J

@K.J. 是的，但通常“父ID”是可空的。 - Conrad Frix

Answer 1

您可以使用此查询临时禁用外键约束：

SET foreign_key_checks = 0;

- Burak Guzel

---

谢谢，这似乎是目前最好的方法。 - K J

这个查询应该在哪里运行？在哪张表上以及如何运行？你最好编辑你的回答并让它更加清晰明了。 - Peyman Mohamadpour

Answer 2

如果在外键上设置了 ON DELETE CASCADE 操作，您应该能够删除自引用的行。

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

与使用“ON DELETE SET NULL”相比，这种方法的好处在于您不必修改模式使“ThreadId”列可为空。

- The Doge Prince

---

我不明白为什么这个答案不是最好和正确的答案。 - RusAlex

Answer 3

有几种解决方法。其他人建议的方法是...

SET foreign_key_checks = 0;

...将禁用每个表的外键。这在共享环境中不适用。

另一种方法是使用

来删除外键。

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

我们可以使用DML对数据进行排序，然后使用以下方法恢复外键：

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

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

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

敏锐的观察者会注意到，我们仍然出现了一种相互依赖的情况，只不过是在记录之间。所以我们并没有真正进步；现在我们有两条无法删除的记录，而不是一条。（顺便说一句，这适用于我们在删除或禁用外键时执行的任何DML）。因此，也许我们需要重新考虑数据模型。我们是在建模循环依赖的图形还是层次结构？

分层数据结构至少需要一个根节点，其他记录可以依赖于它，但它本身不依赖于任何记录。实现这一点的通常方法是使外键列可选。在层次结构的最高级别上，该记录必须在该列中具有NULL值。是否应该只有一个这样的根节点，或者是否允许有多个，取决于您的业务规则。

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

在建模方面，这与一个自成体系的记录没有区别，但它是一种更直观的解决方案。

- APC

Answer 4

无法删除自引用行是MySQL中一个长期存在的已知问题/未解决的功能请求。（参见链接）

在许多出现这个问题的情况下，您可以在执行删除操作之前将外键设置为NULL，以便您的解决方法只会影响您指定的行（使用相同的WHERE子句）。

- hawkip

---

我们的使用情况下最简单的解决方案。谢谢 :) - Ranel Padon

Answer 5

暂时禁用外键

set foreign_key_checks=0;

- XMen

---

看起来应该是 foreign_key_checks，而不是 foreign_key_constraints。 :) - K J

Answer 6

如果您在我的外键上设置了ON DELETE SET NULL，它会让我删除自引用。如果我没有指定ON DELETE，MySQL默认为RESTRICT。

当然，请确保该列是NULLABLE的。根据默认值，您还可以尝试SET DEFAULT。但请记住，NO ACTION在MySQL中只是RESTRICT的别名！

仅在MySQL 5.6上进行了测试（此问题最初发布时尚未发布）。

- CWSpear