用自引用外键删除一行 [英] Deleting a row with a self-referencing foreign key

问题描述

我有一个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`）引用`guestbook`（`Id`）
）ENGINE = InnoDB;

目前表中只有一行：

 
 mysql> select * from留言板; 
 + ----- + ---------- + 
 | Id |线程ID | 
 + ----- + ---------- + 
 | 211 | 211 | 
 + ----- + ---------- + 
 

问题在于没有

 
 mysql>从guestBook删除; 
错误1451（23000）：无法删除或更新父行：外键约束失败（`polaris`.`guestbook`，约束`guestbook_ibfk_1` FOREIGN KEY（`ThreadId`）引用`留言簿' `））
 

由于ThreadId列被定义为非空，因此也不可能将ThreadId临时设置为不同的值以删除行。有没有一种方法可以删除行而不改变表的定义或删除整个表？ 您可以暂时禁用这个查询的外键限制：

  SET foreign_key_checks = 0; 
  

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?

解决方案

You can temporarily disable foreign key constraints with this query:

SET foreign_key_checks = 0;

这篇关于用自引用外键删除一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！