假外键约束失败 [英] Bogus foreign key constraint fail

问题描述

我收到此错误消息:

第40行的错误1217(23000):无法 删除或更新父行: 外键约束失败

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.

这篇关于假外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！