MySQL alter table add foreign key 失败的原因和解决方法

MySQL 是常用的关系型数据库管理系统之一，用于管理大型数据集合。在实际使用中，我们常常需要修改表结构来满足新的需求。在 MySQL 中，我们可以使用 ALTER TABLE 语句来修改表结构。其中，add foreign key 是常用的修改命令之一。但是，有时候添加外键时会失败，本文将深入探讨 MySQL alter table add foreign key 失败的原因和解决方法。

阅读更多：MySQL 教程

什么是外键？

在关系型数据库中，外键是指一个表中的字段，它指向另一个表的字段。外键用于建立两个关系表之间的关联关系，保证数据的一致性和完整性。

举例来说，我们可以建立一张名为“学生”的表和一张名为“课程”的表，它们的数据表示了学生选修哪些课程。在“学生”表中，我们可以添加一个名为“课程编号”的字段，表示学生选修了哪门课程。但是为了表示学生选修的课程的具体信息，我们需要在“课程”表中定义一个“编号”的字段，用于表示该门课程。

这时候，我们需要在“学生”表中添加一个外键，将“学生”表中的“课程编号”字段和“课程”表中的“编号”字段建立关系。这种关系称为“一对多”的关系。

外键的使用限制

虽然外键能够保证数据的一致性和完整性，但是也有一定的使用限制。在 MySQL 中，使用外键有以下几点限制：

外键必须指向已存在的键值，否则添加外键将会失败。
引用外键的表和被引用的表必须使用同一种存储引擎。
外键必须满足参照完整性规则，即被引用的列必须具有唯一索引或主键。
外键关系不能跨越多个数据库。

MySQL alter table add foreign key 失败的原因

在 MySQL 中，添加外键会有许多失败的情况，下面将介绍一些常见的失败原因：

原因一：数据不一致

外键必须指向已存在的键值，如果添加的外键值和被引用的表中不存在的值相对应，添加外键将会失败。例如：

CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `parent` (`id`) VALUES (1);

INSERT INTO `child` (`id`, `parent_id`) VALUES (1, 2);

在上面的例子中，我们创建了两个表“parent”和“child”，“child”表中的“parent_id”为“parent”表的外键。在添加“child”表的外键时，我们将其设为引用“parent”表中一个不存在的值，这时候添加外键将会失败。

原因二：引用的表和被引用的表使用不同的存储引擎

引用外键的表和被引用的表必须使用同一种存储引擎。否则，添加外键将会失败。例如：

CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

在上面的例子中，我们创建了两个表“parent”和“child”，“child”表中的“parent_id”为“parent”表的外键。但是，由于“parent”表和“child”表使用了不同的存储引擎，这时添加外键将会失败。

原因三：被引用的列没有唯一索引或主键

外键必须满足参照完整性规则，即被引用的列必须具有唯一索引或主键。否则，添加外键将会失败。例如：

CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在上面的例子中，我们创建了两个表“parent”和“child”，“child”表中的“parent_id”为“parent”表的外键。但是，“parent”表中的“id”列没有唯一索引或主键，这时添加外键将会失败。

解决 MySQL alter table add foreign key 失败的方法

在 MySQL 中，如果添加外键失败，我们需要根据具体情况进行分析，并采取相应的解决方法。下面将介绍几种常见的解决方法：

解决方法一：检查数据一致性

添加外键时，一定要检查数据的完整性和一致性。确保被引用的列中的值与引用它的列中的值相匹配。我们可以通过以下方法检查数据一致性：

执行以下命令查询引用表中的值：SELECT * FROM parent;
执行以下命令查询被引用表中的值：SELECT * FROM child WHERE parent_id NOT IN (SELECT id FROM parent);

如果被引用表中的某些值没有在引用表中存在，我们需要添加它们的值到引用表中，然后重新添加外键。

解决方法二：修改存储引擎

如果引用外键的表和被引用的表使用了不同的存储引擎，我们需要将它们修改为同一种存储引擎。我们可以通过以下方法修改存储引擎：

执行以下命令查询引用表的存储引擎：SHOW TABLE STATUS WHERE NAME = 'parent';
执行以下命令查询被引用表的存储引擎：SHOW TABLE STATUS WHERE NAME = 'child';
如果发现两个表的存储引擎不同，我们需要将它们修改为同一种存储引擎。

解决方法三：添加唯一索引或主键

如果被引用的列没有唯一索引或主键，我们需要添加一个唯一索引或主键。我们可以通过以下方法添加唯一索引或主键：

执行以下命令添加唯一索引或主键：ALTER TABLE parent ADD PRIMARY KEY (id);
重新添加外键。

总结

MySQL alter table add foreign key 失败可能由多种原因引起，包括数据不一致、引用表和被引用表使用不同的存储引擎、被引用列没有唯一索引或主键等。在实际使用中，我们需要根据具体情况进行分析，并采取相应的解决方法。通过本文的介绍，相信读者已经能够更好地理解和解决 MySQL alter table add foreign key 失败的问题。