MySQL 添加外键失败的解决方法

在 MySQL 中使用 ALTER TABLE 命令添加外键时，可能会遇到添加失败的情况，本文将介绍如何解决 MySQL 中添加外键失败的问题。

阅读更多：MySQL 教程

1. 外键添加失败的原因

1.1 外键列数据类型或长度与主键列不一致

当执行ALTER TABLE语句时，在创建外键关系时，必须保证外键列和主键列的数据类型和长度一致，否则就会添加失败。

例如，下面的语句在执行时，就会报错：

ALTER TABLE t_order ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user(id);

错误信息为：

ERROR 1215 (HY000): Cannot add foreign key constraint

这是因为 t_order 表中的 user_id 列和 t_user 表中的 id 列的数据类型或长度不一致导致的。

1.2 外键列没有创建索引

在创建外键关系时，必须为外键列创建索引，否则就会添加失败。

例如，下面的语句在执行时，就会报错：

ALTER TABLE t_order ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user(id);

错误信息为：

ERROR 1215 (HY000): Cannot add foreign key constraint

这是因为 t_order 表中的 user_id 列没有创建索引导致的。可以通过使用 CREATE INDEX 语句为该列创建索引，然后再次执行 ALTER TABLE 语句即可。

1.3 外键列中存在不存在的值

在创建外键关系时，外键列中不能存在主键列中不存在的值，否则就会添加失败。

例如，下面的语句在执行时，就会报错：

ALTER TABLE t_order ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES t_user(id);

错误信息为：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

这是因为 t_order 表中的 user_id 列中存在 t_user 表中不存在的值导致的。

2. 解决外键添加失败的方法

2.1 检查外键列和主键列的数据类型和长度是否一致

可以使用 SHOW CREATE TABLE 命令查看表结构，检查外键列和主键列的数据类型和长度是否一致。

例如，下面的语句查看 t_user 表的结构：

SHOW CREATE TABLE t_user;

结果为：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

可以看到，t_user 表中的 id 列的数据类型为 int(11)，所以在创建外键关系时，必须保证 t_order 表中的 user_id 列的数据类型也为 int(11) 才能添加成功。

2.2 为外键列创建索引

可以使用 CREATE INDEX 命令为外键列创建索引。

例如，下面的语句为 t_order 表中的 user_id 列创建索引：

CREATE INDEX idx_order_user_id ON t_order(user_id);

然后再次执行 ALTER TABLE 语句即可。

2.3 清理外键列中的不存在的值

可以使用以下语句查询 t_order 表中的 user_id 列中不存在于 t_user 表中的 id 值：

SELECT user_id FROM t_order WHERE user_id NOT IN (SELECT id FROM t_user);

然后可以将这些无效值删除：

DELETE FROM t_order WHERE user_id NOT IN (SELECT id FROM t_user);

然后再次执行 ALTER TABLE 语句即可。

总结

在 MySQL 中添加外键时，如果添加失败，可以检查外键列和主键列的数据类型和长度是否一致，是否为外键列创建了索引，以及外键列中不存在主键列中的值。根据具体情况选择相应的解决方法来解决外键添加失败的问题。