如何处理 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
....

原文链接:

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Netkiller

数据库安全·内容版本控制,撰改留痕

以下节选择《Netkiller Architect 手札》 地址 http://www.netkiller.cn/architect/ 接下来几周的话题是数据库...

2994
来自专栏数据分析

[数据库基础]——快速浏览日期时间转换

阅读导航 数据库日期和时间类型 相互转换     time ⇌ date time ⇌ smalldatetime time ⇌ datetime tim...

2796
来自专栏Netkiller

重新整理AUTO_INCREMENT字段

节选择《Netkiller MySQL 手札》 13.9. 重新整理AUTO_INCREMENT字段 AUTO_INCREMENT 并非按照我们意愿,顺序排列,...

2896
来自专栏听雨堂

一次数据库的整理的sql语句

//查询以井结束的记录 SELECT f_wellnumber, SUBSTRING(f_wellnumber, 1, LEN(f_wellnumber) - ...

1859
来自专栏Netkiller

重新整理AUTO_INCREMENT字段

节选择《Netkiller MySQL 手札》 13.9. 重新整理AUTO_INCREMENT字段 AUTO_INCREMENT 并非按照我们意愿,顺序排列...

3455
来自专栏IT开发技术与工作效率

MySQL查询表位置和列注释等

1332
来自专栏北京马哥教育

zabbix表分区(适用于zabbix2.0.x,zabbix2.2.x和zabbix2.4.x)[推荐]

本文主要介绍了zabbix进行数据库表分区的方法: 在系统监控中,zabbix已经代替了nagios+cacti,zabbix以其良好的图形展示和高度自定义赢得...

3566
来自专栏康怀帅的专栏

MySQL 查找数据 SELECT

SELECT 字段1,字段2 FROM 表名; SELECT 表名.字段名 FROM 表名; 别名 SELECT 字段 AS 别名 FROM 表名; 偏移量 S...

2816
来自专栏文渊之博

mysql replace into 的使用情况

 发现,auto_increment并没有+1,而是针对原来的那一条id=4的记录进行了update,因为没有指定其他列(v,extra)的值,所以,updat...

783
来自专栏kwcode

SQL 存储过程分页

CREATE PROC p_Team_GetTemaList @pageindex INT , @pagesize INT , @key...

3118

扫码关注云+社区