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

问题描述

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

<上一页>创建表`留言簿`(`Id` int(10) 无符号非空，`ThreadId` int(10) unsigned NOT NULL,主键(`Id`)，KEY `ThreadId` (`ThreadId`),约束`guestbook_ibfk_1`外键(`ThreadId`)参考`guestbook`(`Id`)) 引擎=InnoDB;

目前表格中只有 1 行:

<上一页>mysql> 从留言簿中选择 *；+-----+----------+|身份证 |线程 ID |+-----+----------+|211 |211 |+-----+----------+

问题是没有办法在不破坏约束的情况下删除这一行.

<上一页>mysql> 从留言簿中删除；错误 1451 (23000):无法删除或更新父行:外键约束失败(`polaris`.`guestbook`, CONSTRAINT`guestbook_ibfk_1` FOREIGN KEY (`ThreadId`) REFERENCES`guestbook` (`Id`))

由于 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屋！