如何处理 MySQL错误码 1215：无法添加外键约束？

本文为作者翻译文章，原文链接:Dealing with MySQL Error Code 1215: “Cannot add foreign key constraint”

在给一个表创建外键时，MySQL总是会出现提示：

ERROR 1215 (HY000): Cannot add foreign key constraint

这信息基本是啥都没说，下面就来说说几种常见的导致1215错误的情况：

1. 父表不存在 mysql> CREATE TABLE child ( -> id INT(10) NOT NULL PRIMARY KEY, -> parent_id INT(10), -> FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) -> ) ENGINE INNODB; ERROR 1215 (HY000): Cannot add foreign key constraint mysql> SHOW TABLES LIKE 'par%'; Empty set (0.00 sec) 解决方法：
   • 先创建父表，再创建子表；
   • SET FOREIGN_KEY_CHECKS=0;后，创建子表，再创建父表；SET FOREIGN_KEY_CHECKS=1;（这备份常用方式）
2. 标点符号使用不对 错误方式: ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`; 正确方式: ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`); ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id); ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`);
3. 父表或者父表中相关列的名字写错了错误: ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id); 正确: ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);
4. 父列和子列数据类型不一致mysql> CREATE TABLE parent ( -> id INT(10) NOT NULL PRIMARY KEY, -> column_1 INT(10) NOT NULL, -> column_2 INT(10) NOT NULL, -> column_3 INT(10) NOT NULL, -> column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, -> KEY column_2_column_3_idx (column_2, column_3), -> KEY column_4_idx (column_4) -> ) ENGINE INNODB; Query OK, 0 rows affected (0.00 sec) # 错误：父表中id是int类型，子表中parent_id是bigint类型 CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id BIGINT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) ) ENGINE INNODB;
5. 父表中相关列上没有任何索引mysql> CREATE TABLE parent ( -> id INT(10) NOT NULL PRIMARY KEY, -> column_1 INT(10) NOT NULL, -> column_2 INT(10) NOT NULL, -> column_3 INT(10) NOT NULL, -> column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, -> KEY column_2_column_3_idx (column_2, column_3), -> KEY column_4_idx (column_4) -> ) ENGINE INNODB; #错误：因为父表column_1列上没有任何索引 CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_1 INT(10), FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`) ) ENGINE INNODB; #正确 ALTER TABLE parent ADD INDEX column_1_idx(column_1); CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_1 INT(10), FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`) ) ENGINE INNODB;
6. 父表相关列有索引，但索引是一个多列索引，同时该列不是该做索引最左列mysql> CREATE TABLE parent ( -> id INT(10) NOT NULL PRIMARY KEY, -> column_1 INT(10) NOT NULL, -> column_2 INT(10) NOT NULL, -> column_3 INT(10) NOT NULL, -> column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, -> KEY column_2_column_3_idx (column_2, column_3), -> KEY column_4_idx (column_4) -> ) ENGINE INNODB; #错误：因为父表column_3列不是column_2_column_3_idx索引的最左列 CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_3 INT(10), FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`) ) ENGINE INNODB; #正确 ALTER TABLE parent ADD INDEX column_3_idx(column_3); CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_3 INT(10), FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`) ) ENGINE INNODB;
7. 父表和子表表(列)的字符集 或 排序集 不同mysql> CREATE TABLE parent ( -> id INT(10) NOT NULL PRIMARY KEY, -> column_1 INT(10) NOT NULL, -> column_2 INT(10) NOT NULL, -> column_3 INT(10) NOT NULL, -> column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, -> KEY column_2_column_3_idx (column_2, column_3), -> KEY column_4_idx (column_4) -> ) ENGINE INNODB; #错误：因为子表parent_column_4 列排序集是utf8_unicode_ci而父表column_4列排序集是utf8_bin CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`) ) ENGINE INNODB; #正确 CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`) ) ENGINE INNODB;
8. 父表不是innodbCREATE TABLE `parent` ( `id` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; #修改parent表为innodb ALTER TABLE parent ENGINE=INNODB;
9. reference 后只有父表表名，没有列信息#错误 CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, column_2 INT(10) NOT NULL, FOREIGN KEY (column_2) REFERENCES parent ) ENGINE INNODB; #正确 CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, column_2 INT(10) NOT NULL, FOREIGN KEY (column_2) REFERENCES parent(column_2) ) ENGINE INNODB;
10. 父表是分区表CREATE TABLE `parent` ( `id` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (id) PARTITIONS 6 */ #删除分区 ALTER TABLE parent REMOVE PARTITIONING;
11. 父表中相关列是一个“虚拟列”而不是实际存储列（5.7版本以上）

CREATE TABLE parent (
  id INT(10) NOT NULL PRIMARY KEY,
  column_1 INT(10) NOT NULL,
  column_2 INT(10) NOT NULL,
  column_virt INT(10) AS (column_1 + column_2) NOT NULL,
  KEY column_virt_idx (column_virt)
) ENGINE INNODB;
#修改方法
ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL;
#正确
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_virt INT(10) NOT NULL,
  FOREIGN KEY (parent_virt) REFERENCES parent(column_virt)
) ENGINE INNODB;

创建外键失败的更多提示信息：show engine innodb status

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(15) NOT NULL DEFAULT '',
  KEY `idx_name_id` (`name`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `consume` (
  `uid` int(11) NOT NULL,
  `money` float NOT NULL DEFAULT '0',
  KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---------------------------
mysql>alter table consume add FOREIGN KEY (uid) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE cascade;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql>show engine innodb status\G
....
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2017-06-14 21:33:55 7f6fabab7700 Error in foreign key constraint of table luke_demo/#sql-36c5_490e:
FOREIGN KEY (uid) REFERENCES `user`(`id`) ON DELETE cascade ON UPDATE cascade:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
....