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

Posted on Views:

本文主要研究一下MySql 1215的错误: “Cannot add foreign key constraint”

我们在设计多表关联时，经常会需要用到外键，也就是当前表引用其他表的主键。但有时候哪里写的不对，MySQL会提示1215错误。但我们并不知道是什么原因导致的错误。

我们来分析常见的情况:

表或者约束引用的索引不存在(通常出现在载入存储文件)

如何诊断: 运行SHOW TABLE或者SHOW CREATE TABLE。如果出现1146错误，意味着表没有按顺序创建
如何解决: 运行CREATE TABLE或者临时禁用外键

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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)

表或者约束引用的索引错误引号

如何诊断: 检查所有的外键，确保所有的引用正确
如何解决: 加上缺少的引号

例子:

1
2
3
4
5
6
7
8
9
10
11
# 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`);

这里需要注意引用表需要加上对应的列名，不能因为当前表的列名与引用表的主键名一致而忽略。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table department
	(dept_name		varchar(20), 
	 building		varchar(15), 
	 budget		        numeric(12,2) check (budget > 0),
	 primary key (dept_name)
	);
create table course
	(course_id		varchar(8), 
	 title			varchar(50), 
	 dept_name		varchar(20),
	 credits		numeric(2,0) check (credits > 0),
	 primary key (course_id),
	 foreign key (dept_name) references department(dept_name)
		on delete set null
	) ;

这里如果references后面的表明没有写上列名，会报错

约束引用的键或表名称写错

如何诊断: 运行SHOW TABLES和SHOW COLUMNS比较声明的引用
如何解决: 写上正确的名称

例子:

1
2
3
4
5
# 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);

约束引用的外键类型和长度不一致

如何诊断: 运行SHOW CREATE TABLE parent检查所有引用类型和长度一致
如何解决: 修改引用类型和长度

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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;

外键不是KEY类型的任意一种

如何诊断: 使用SHOW CREATE TABLE parent来检查引用的列是正确的
如何解决: 使引用的列加上KEY,UNIQUE KEY或者PRIMARY KEY的一种

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 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;

引用多个外键

如何诊断:运行SHOW CREATE TABLE parent检查多引用外键的正确性
如何解决:给最左的键加上索引

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 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;

两个表或者列使用不同的字符集或者排序

如何诊断: 比较父类表和子类表的字符集和排序
如何解决: 修改子类的字符集和排序

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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;

父类表没有使用InnoDB

如何诊断: 运行SHOW CREATE TABLE parent，检查是否是InnoDB
如何修复:修改父类表，使其采用InnoDB

例子:

1
2
3
4
5
6
# 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;

使用简短语法引用外键

如何诊断: 检查引用部分, MySQL不支持简短语法
如何解决: 修改子类表，规定引用的表和列

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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;

父表被分割了

如何诊断: 检查父表是否被分割
如何修复: 合并被分割的部分

例子:

1
2
3
4
5
6
7
8
9
# 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;

引用的列是虚列

如何诊断: 检查引用的列是否是虚列
如何修复: 修改父表的列，确保列不是虚列

例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 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;

对约束行为使用默认集

如何诊断: 检查使用约束行为的字表的行为，尝试使用SET DEFAULT
如何修复: 移除SET DEFAULT

例子

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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;

总结

通过以上的这些诊断基本上能够解决1215这个错误。关于外键的更多信息可以参考Mysql外键约束

参考: Dealing with MySQL Error Code 1215: “Cannot add foreign key constraint”