MySQL Cannot Add Foreign Key Constraint 错误如何修复？

已发表: 2022-12-22

如何修复“ MySQL 无法添加外键约束”错误？ 尝试向 MySQL 表添加外键约束时，您可能会收到以下错误消息：“ ERROR 1215 (HY000): Cannot add foreign key constraint ”。

像这样的错误非常普遍，并没有提供有关 MySQL 无法将外键约束插入表的具体原因的具体细节。要解决此问题，您需要确定问题的确切原因。

本文将通过四种行之有效的方法解释MySQL出现“#1215 – Cannot Add Foreign Key Constraint”错误的原因，如何查找原因，以及如何修复。

MySQL Cannot Add Foreign Key Constraint 错误如何修复？

MySQL报“#1215 – Cannot Add Foreign Key Constraint”错误是什么原因？

此错误可能有多种原因。为了确定此错误的原因，最好检查 SHOW ENGINE INNODB STATUS 的 LATEST FOREIGN KEY ERROR 部分。

这样，您将对问题有更好的理解。您将能够从以下列表中识别您的案例：

通常，在加载转储时，约束所指的表或索引不存在

有时，客户希望添加约束的数据库表不存在，导致出现 1215 错误信息。

但是，没有迹象表明该表不存在，因此建议您使用该命令来确定数据库中有哪些表可用。

在每个父表上运行 SHOW TABLES 或 SHOW CREATE TABLE。任何返回错误 1146 的表都表示这些表的创建顺序不正确。

解决方案是运行缺少的 CREATE TABLE 并重试或暂时禁用外键检查。在可能存在循环引用的备份恢复期间执行此步骤是必不可少的。

运行以下代码来修复错误：

 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; -- restore your backup within THIS session SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

约束引用中的表或索引滥用引号

如果您希望诊断此错误，请检查每个 FOREIGN KEY 声明并确保对象限定符周围没有引号，或者表格周围有引号并且每个列名称周围有一对。

这可以通过不引用任何内容或分别引用表和列来解决。

约束引用中的本地键、外部表或列有拼写错误

可以通过运行 SHOW TABLES 和 SHOW COLUMNS 并将字符串与 REFERENCES 声明中指定的字符串进行比较来诊断此错误。错别字一经发现应及时更正。

约束引用的列与外部列的类型或宽度不同

对于诊断，使用 SHOW CREATE TABLE parent 来确保本地列和引用列包含相同类型的数据和宽度。

可以通过编辑 DDL 语句来解决该问题，以确保子表中的列定义与父表中的列定义相匹配。

异物不是任何类型的 KEY

要诊断此问题，请使用 SHOW CREATE TABLE parent 检查表的 REFERENCES 部分以确保指向它的列未被索引。

通过使该列成为 KEY、UNIQUE KEY 或 PRIMARY KEY 来解决问题。

外键是多列PK或UK，其中引用的列不是最左边的

为了诊断此错误，执行 SHOW CREATE TABLE parent 命令以确定 REFERENCES 部分是否指向某个列，该列存在于某些多列索引中但不是其定义中的最左边。

您可以通过向父表添加索引来解决此问题，其中引用的列是最左边或唯一的列。

两个表/列之间的不同字符集/排序规则

要诊断问题，请运行 SHOW CREATE TABLE parent 并验证子列的 CHARACTER SET 和 COLLATE 部分是否与父表上的部分匹配。

要解决此问题，请确保子表 DDL 与其父表/列的字符集和排序规则相匹配，或者更改父表以使其定义与子表的定义相匹配。

父表没有使用 InnoDB

可以通过运行 SHOW CREATE TABLE parent 并检查 ENGINE=INNODB 来诊断该问题。

更改父表以从 MySQL 切换到 InnoDB 以解决此问题。

如何查找“无法添加外键约束”错误的原因？

这个错误并没有说明MySQL无法对表进行外键约束的具体原因，很笼统。

为帮助解决此问题，您需要确定确切原因。您可以通过在 MySQL 客户端中运行以下查询来执行此操作：

 SHOW ENGINE INNODB STATUS;

此查询将返回三个字段：类型、名称和状态。 LATEST FOREIGN KEY ERROR 可以通过展开结果的状态列来识别。

本节旨在确定上次更改查询失败的原因，以便您可以解决问题。

导致此问题的原因通常是主表和外部表中的列类型不匹配。

问题也可能是两个表的引擎类型不匹配引起的，例如 MyISAM 和 InnoDB。

这也可能是由于表之间的排序规则不同，一个使用 UTF-8，另一个使用 latin1。

您应该注意以下因素：

Engine ：每张表应该配置相同的数据库引擎，例如InnoDB。

数据类型：在一个表中，两列应具有相同的“ datatype. int(11) datatype. int(11) ”，而在另一个例子中， smallint(5)会导致问题。

Collation ：为了确保兼容性，两个列的字符集应该相同，例如，UTF8。

注意：列仍然可以有不同的列，即使您的表具有相同的排序规则。

Unique ：外键应该引用引用表中的唯一字段，通常是主字段。

Ungisned ：一个表可能有未签名的键，而另一个表可能没有任何未签名的键。

如何修复MySQL“#1215 – Cannot Add Foreign Key Constraint”错误（4种方法）？

讨论了导致此错误消息的不同原因后，我们进入了下一步。这个错误需要修复，让我们看看如何用四种有效的方法来修复它。

方法 1：检查表可用性

要开始排查错误，您可以通过运行以下命令检查该表是否存在：

 show tables;

从这段代码中可以明显看出，由于表可用，因此可以创建约束。

方法 2：确保父表正在使用 InnoDB

可以通过检查数据库表的存储引擎集来进一步调查。可以使用以下命令来执行此操作：

 SHOW CREATE TABLE parent

结果是你会发现 InnoDB 不是存储引擎。由于设置了 ENGINE=MYISAM，您可以通过运行以下命令来更改存储引擎：

 ALTER TABLE parent ENGINE=INNODB;

最终可以使用此方法修复错误。

方法三：检查是否有不当引用

对于每个 FOREIGN KEY 声明，请确保没有在对象限定符周围插入引号，或者在表名和列名周围使用单独的引号。

使用此方法，必须不引用任何内容或分别引用列和表以修复错误。

方法四：验证密钥是否存在

在输入MySQL命令时，需要保证输入了正确的命令。交叉检查确保客户没有引用目标表中不存在的表键。

因此，有必要纠正客户如何使用桌子上的钥匙。在某些情况下，用于 ON DELETE SET NULL 的列可能没有空定义。

为防止 MySQL 错误 1215，您必须确保该列设置为默认空值。

结论

本文通过四种行之有效的方法解释了MySQL出现“#1215 – Cannot Add Foreign Key Constraint”错误的原因、如何查找原因以及如何修复。

感谢您抽出宝贵时间审阅本文。我们很高兴向您展示它。在评论部分，请发表任何问题或意见。

如果您想了解最新消息，请喜欢并关注我们的 Facebook 和 Twitter 页面。