MySQL添加外键提示#1005 errno:150约束格式错误如何排查

问题复现 #

执行外键创建语句：

ALTER TABLE activity_log_admins ADD FOREIGN KEY (adminid) REFERENCES admins(adminid)

返回错误：

#1005 - Can't create table miu.activity_log_admins (errno: 150 "Foreign key constraint is incorrectly formed")

常见触发原因及对应解决方法 #

这个报错本质是外键约束创建时的合法性校验不通过，按出现概率从高到低排查即可：

• 关联字段属性不匹配
  这是最高发的原因，外键要求关联的两个字段数据类型、长度、符号属性（有符号/无符号）、字符集、排序规则必须完全一致。比如admins.adminid是INT UNSIGNED，但activity_log_admins.adminid是普通有符号INT，就会直接触发报错。
  排查方式：执行以下语句查看两个字段的定义

  DESC admins;
  DESC activity_log_admins;
  

  对比两个adminid字段的所有属性，将activity_log_admins.adminid修改为和admins.adminid完全一致即可（外键字段不需要配置自增属性）。

• 被引用字段无有效索引
  MySQL要求被引用的父表字段必须是主键，或者配置了唯一索引，普通非唯一索引无法通过外键校验。
  排查方式：查看admins表索引，确认adminid是主键或有唯一约束。如果没有，执行以下语句添加主键：

  ALTER TABLE admins ADD PRIMARY KEY (adminid);
  

• 表存储引擎不支持外键
  只有InnoDB存储引擎支持外键约束，如果任意一张表用的是MyISAM、MEMORY等不支持外键的引擎，就会创建失败。
  排查方式：执行以下语句查看两张表的存储引擎

  SHOW TABLE STATUS WHERE Name IN ('admins','activity_log_admins');
  

  如果Engine列不是InnoDB，执行语句修改引擎：

  ALTER TABLE admins ENGINE = InnoDB;
  ALTER TABLE activity_log_admins ENGINE = InnoDB;
  

• 子表存在脏数据
  创建外键时MySQL会全表校验子表数据，如果activity_log_admins.adminid中存在admins.adminid里没有的值，会因为数据合法性校验不通过报错。
  排查方式：执行以下语句查询脏数据

  SELECT ala.adminid 
  FROM activity_log_admins ala 
  LEFT JOIN admins a ON ala.adminid = a.adminid 
  WHERE a.adminid IS NULL;
  

  把查询到的无效adminid对应的记录删除，或者在admins表中补全对应adminid的记录后，再重新创建外键即可。

• 外键约束名重复
  同一个数据库下外键约束名不能重复，如果自动生成的外键名已经被其他表占用，也会触发报错。可以手动指定唯一的外键名规避：

  ALTER TABLE activity_log_admins ADD CONSTRAINT fk_activity_log_admins_adminid FOREIGN KEY (adminid) REFERENCES admins(adminid);
  

内容的提问来源于stack exchange，提问作者Ngode Danuel