Cannot add foreign key constraint

雨点打透心脏的1/2处 2022-02-21 00:23 229阅读 0赞

协助开发人员解决了两则无法创建外键约束的case，MySQL客户端报错信息为`ERROR 1215 (HY000): Cannot add foreign key constraint`，在navicat的报错信息为`Can't create table‘..’ （errno:150）`。

Case 1  
添加外键约束：

alter table b add constraint fk_month_budget_plan_id 
    foreign key(month_budget_plan_id) references a(id);

遭遇报错：  
`ERROR 1215 (HY000): Cannot add foreign key constraint`

检查表结构如下：

CREATE TABLE `a` (
      `id` int(11) unsigned NOT NULL auto_increment COMMENT '主键id',
      ......
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='月度预算编制';
    
    CREATE TABLE `b` (
      `id` int(11) unsigned NOT NULL auto_increment COMMENT '主键id',
      `month_budget_plan_id` int(11) default NULL COMMENT '月度预算编制id',
      ......
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='月度预算编制明细';

首先确认外键约束关系的两个字段字符集一致，但外键字段`month_budget_plan_id`比被引用键少了unsigned，并没有完全一致，所以导致无法创建外键。  
小结：创建外键时，外键字段的字符集，类型，和unsigned属性必须要与被引用键一致，否则无法创建；对于varchar类型，不强制要求长度一致。

Case 2  
添加外键约束：

alter table b add constraint fk_ecommerce_channel_code
    foreign key(ecommerce_channel_code) references a(code);

遭遇报错：  
`ERROR 1215 (HY000): Cannot add foreign key constraint`

检查表结构如下：

CREATE TABLE `b` (
      `id` int(11) unsigned NOT NULL auto_increment COMMENT '主键id',
      `ecommerce_channel_code` varchar(32) NOT NULL COMMENT '电商渠道编码',
      ...... 
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='月度预算结果';
    
    CREATE TABLE `a` (
      `id` int(11) unsigned NOT NULL auto_increment COMMENT '主键id',
      `code` varchar(32) NOT NULL COMMENT '渠道编码',
      ......
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电商渠道';

首先确认外键约束关系的两个字段字符集，类型都一致，但检查发现被引用键a.code字段上没有索引，所以导致无法创建外键。  
小结：被引用键上必须要有索引，否则无法创建外键约束；在Case 1中不受索引的影响是由于被引用键是主键，本身就有一个主键索引。

总结一下外键约束的注意事项：  
1.外键上无法插入主表没有的数据，也无法更新为主表没有的数据；  
2.外键上必须要有索引，主表上对应的列最好也创建索引，因为当子表外键插入数据时，也是需要到主表对应的列上去检查数据是否存在，有索引可以提高效率；  
3.如果一个表被其他表的外键引用，则这个表无法被删除；如果通过set foreign\_key\_checks=0强制删除主表，则子表无法插入任何数据；  
4.外键的字段类型，字符集必须要跟引用的字段一致，unsigned也要一直，否则无法创建外键约束；  
5.被引用的列上必须要有索引，否则无法创建外键约束。