虚假外键约束失败

2021-11-20 00:00:00 mysql innodb

我收到此错误消息:

错误 1217 (23000) 在第 40 行:不能删除或更新父行:a外键约束失败

ERROR 1217 (23000) at line 40: Cannot delete or update a parent row: a foreign key constraint fails

...当我尝试删除桌子时:

... when I try to drop a table:

DROP TABLE IF EXISTS `area`;

... 定义如下:

CREATE TABLE `area` (
  `area_id` char(3) COLLATE utf8_spanish_ci NOT NULL,
  `nombre_area` varchar(30) COLLATE utf8_spanish_ci NOT NULL,
  `descripcion_area` varchar(100) COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `nombre_area_UNIQUE` (`nombre_area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

有趣的是，我已经删除了架构中具有针对 area 的外键的所有其他表.实际上，除了area表外，数据库是空的.

The funny thing is that I already dropped all other tables in the schema that have foreign keys against area. Actually, the database is empty except for the area table.

如果数据库中没有任何其他对象，它怎么可能有子行?据我所知，InnoDB 不允许在其他模式上使用外键，是吗?

How can it possibly have child rows if there isn't any other object in the database? As far as I know, InnoDB doesn't allow foreign keys on other schemas, does it?

(我什至可以运行 RENAME TABLE area TO something_else 命令:-?)

(I can even run a RENAME TABLE area TO something_else command :-?)

推荐答案

两种可能:

  1. 在另一个模式(mysql 术语中的数据库")中有一个表，它有一个 FK 引用
  2. innodb 内部数据字典与 mysql 数据字典不同步.

在删除失败后，您可以通过执行SHOW ENGINE INNODB STATUS"来查看它是哪个表(无论如何是其中之一).

You can see which table it was (one of them, anyway) by doing a "SHOW ENGINE INNODB STATUS" after the drop fails.

如果结果是后一种情况，如果可以，我会转储并恢复整个服务器.

If it turns out to be the latter case, I'd dump and restore the whole server if you can.

MySQL 5.1 及更高版本将在错误消息中为您提供带有 FK 的表的名称.

MySQL 5.1 and above will give you the name of the table with the FK in the error message.

相关文章