MySQL Error Code 1215: "Cannot add foreign key constraint"

MySQL Error Code 1215: “Cannot add foreign key constraint”

对于这种看似简单的报错：

ERROR 1215 (HY000): Cannot add foreign key constraint

可能会有多种原因。

对于这种错误，最好的方法就是查看show engine innodb status中的latest foreign key error部分的内容。

1.约束所引用的表或索引尚不存在（通常在加载转储时）

如何诊断：对父表执行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;

示例：

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
​
# We check for the parent table and is not there.
mysql> SHOW TABLES LIKE 'par%';
Empty set (0.00 sec)
​
# We go ahead and create the parent table (we’ll use the same parent table structure for all other example in this blogpost):
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)
​
# And now we re-attempt to create the child table
mysql> CREATE TABLE child (
    ->   id INT(10) NOT NULL PRIMARY KEY,drop table child;
    ->   parent_id INT(10),
    ->   FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
    -> ) ENGINE INNODB;
Query OK, 0 rows affected (0.01 sec)

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

如何诊断：检查每个FOREIGN KEY声明并确保对象限定符没有引号，或者表有引号并且列名有一对单独的引号。

如何解决：都不使用引号，或者将表和列名各自使用引号

示例：

# wrong; single pair of backticks wraps both table and column
ALTER TABLE child  ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`;
​
# correct; one pair for each part
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`);
​
# also correct; no backticks anywhere
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);
​
# also correct; backticks on either object (in case it’s a keyword)
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`);

3.约束引用中的本地键、外部表或列有错字

如何诊断：执行show tables、show columns进行比较

如何解决：找出并修复错字

示例：

# wrong; Parent table name is ‘parent’
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id);
​
# correct
ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id);

4.约束中引用的列的类型或者长度与被引用的列不同

如何诊断：执行 SHOW CREATE TABLE parent检查本来地的列和引用的列是否有相同的类型和长度

如何解决：修改ddl语句，使得二者相互匹配

示例：

# wrong; id column in parent is INT(10)
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; 
​
# correct; id column matches definition of parent table
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY, 
  parent_id INT(10) NOT NULL, 
  FOREIGN KEY (parent_id) REFERENCES `parent`(`id`)
) ENGINE INNODB;

5.外部对象不是任何类型的key

如何诊断：执行 SHOW CREATE TABLE parent检查被引用的部分指向的列

如何解决：确保key、或unique key、或primary key在父表上是存在的

示例：

# wrong; column_1 is not indexed in our example table
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;
​
# correct; we first add an index and then re-attempt creation of child table
ALTER TABLE parent ADD INDEX column_1_idx(column_1);
​
# and then re-attempt creation of child table
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.外键是多个列组成的主键或唯一键，而被引用的列不是最左侧的列

如何诊断：show create table parent检查references指向的列出现在多列索引的位置

如何解决：在父表上增加一个索引，满足被引用的列是在索引的最左侧

示例：

# wrong; column_3 only appears as the second part of an index on parent table
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;
​
# correct; create a new index for the referenced column
ALTER TABLE parent ADD INDEX column_3_idx (column_3);
​
# then re-attempt creation of child
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.两个表或列使用不同的字符集或排序规则

如何诊断：show create table 父表和子表的character set、collate定义是否一致

如何解决：修改表定义，一般是修改子表

示例：

# wrong; the parent table uses utf8/utf8_bin for charset/collation 
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;
​
# correct; edited DDL so COLLATE matches parent definition
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.父表使用的不是innodb引擎

如何诊断：show create table parent检查引擎类型

如何解决：修改表定义

示例：

# wrong; the parent table in this example is MyISAM:
CREATE TABLE parent (
  id INT(10) NOT NULL PRIMARY KEY
) ENGINE MyISAM;
​
# correct: we modify the parent’s engine 
ALTER TABLE parent ENGINE=INNODB;

9.使用语法简写来引用外键

如何诊断：检查references部分是否只是包含了表名字

如何解决：修改表定义

示例：

# wrong; only parent table name is specified in REFERENCES
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  column_2 INT(10) NOT NULL,
  FOREIGN KEY (column_2) REFERENCES parent
) ENGINE INNODB;
​
# correct; both the table and column are in the REFERENCES definition
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.父表是分区表

如何诊断：检查父表是否是分区表

如何解决：移除分区定义

示例：

    # wrong: the parent table we see below is using PARTITIONs
CREATE TABLE parent (
  id INT(10) NOT NULL PRIMARY KEY
) ENGINE INNODB
PARTITION BY HASH(id)
PARTITIONS 6;
​
#correct: ALTER parent table to remove partitioning
ALTER TABLE parent REMOVE PARTITIONING;

11.引用的列是生成的虚拟列(5.7之后才会有)

如何诊断： SHOW CREATE TABLE parent检查是否是虚拟列

如何解决：修改父表，将虚拟列变成真正的列

示例：

# wrong; this parent table has a generated virtual column
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;
​
# correct: make the column STORED so it can be used as a foreign key
ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL;
​
# And now the child table can be created pointing to column_virt
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;

12.为约束设置默认值

如何诊断：查看表是否on delete、on update约束而设置的set default

如何解决：移除或修改set default语句

示例：

# wrong; the constraint action uses SET DEFAULT
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_id INT(10) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET DEFAULT
) ENGINE INNODB;        
​
# correct; there's no alternative to SET DEFAULT, removing or picking other is the corrective measure
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_id INT(10) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent(id) 
) ENGINE INNODB; 

13.对not null的列，设置set null约束

如何诊断：查看表是否有not null约束

如何解决：如果表已经存在，使用alter、modify移除not null

示例：

# wrong; the constraint column uses NOT NULL
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_id INT(10) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET NULL
) ENGINE INNODB;        
​
# correct; make the parent_id column accept NULLs (i.e. remove the NOT NULL)
CREATE TABLE child (
  id INT(10) NOT NULL PRIMARY KEY,
  parent_id INT(10),
  FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET NULL
) ENGINE INNODB;