MySQL添加外键约束报#1215 cannot add foreign key constraint问题咨询

问题复现 #

数据库存在countries和country_continents两张表，需要将countries表的continent_id字段设为外键，关联country_continents表的id字段。执行以下SQL时报错：

ALTER TABLE countries
    ADD CONSTRAINT fk_continent_id
    FOREIGN KEY (continent_id)
    REFERENCES country_continents(id);

错误信息：
#1215 - cannot add foreign key constraint

此前因为出现"Error: relational features are disabled"报错，已经执行如下命令将两张表的引擎修改为支持外键的InnoDB：

ALTER TABLE countries ENGINE=InnoDB;
ALTER TABLE country_continents ENGINE=InnoDB;

country_continents表结构如下：

countries表结构如下：

常见报错原因 #

• 关联字段属性不匹配：country_continents的id字段和countries的continent_id字段类型、长度、是否无符号、是否允许为空等属性不一致，是该错误最常见的诱因。
• 父表关联字段无唯一索引：作为外键关联目标的country_continents.id必须是主键或者设置了唯一索引，否则无法创建关联。
• 表/字段字符集、排序规则不一致：两张表或者关联字段的字符集、排序规则不同，也会触发约束创建失败。
• 子表存在不符合约束的脏数据：countries表中存在continent_id的值不在country_continents.id取值范围内的记录，导致约束无法生效。
• 外键约束名重复：当前数据库中已经存在名为fk_continent_id的约束，重名导致创建失败。

解决步骤 #

1. 核对关联字段属性：执行以下命令查看两个字段的属性，调整continent_id的属性和id完全一致即可

   DESC country_continents id;
   DESC countries continent_id;
   

2. 确认父表关联字段属性：检查country_continents.id是否为主键或存在唯一索引，漏设的话执行以下语句补上

   ALTER TABLE country_continents ADD PRIMARY KEY(id);
   

3. 核对字符集配置：执行以下命令查看两张表的字符集和排序规则，不一致的话统一调整

   SHOW CREATE TABLE country_continents;
   SHOW CREATE TABLE countries;
   -- 统一字符集示例
   ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
   

4. 清理脏数据：执行以下语句查询不符合约束的记录，删除或修正为合法值即可

   SELECT * FROM countries WHERE continent_id NOT IN (SELECT id FROM country_continents);
   

5. 若为约束名重复，修改SQL中的约束名（例如改为fk_countries_continent_id）后重新执行即可。

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