mysql新增外键时报错：1215 Cannot add foreign key constraint解决方法

新建两张表city和province

#USE db2;

#表city
CREATE TABLE city(cid INT PRIMARY KEY ,city_name VARCHAR(20),city_province VARCHAR(20))

ENGINE=INNODB DEFAULT CHARSET=utf8;

#表province
CREATE TABLE province(pid INT PRIMARY KEY ,province_name VARCHAR(20)) ENGINE=INNODB DEFAULT CHARSET=utf8;

 

#在表city中新增外键city_province_id_fk1
ALTER TABLE city ADD CONSTRAINT city_province_id_fk1 FOREIGN KEY(id) REFERENCES province(id);
#执行正常

#在表city中新增外键city_province_name_fk2
ALTER TABLE city ADD CONSTRAINT city_province_name_fk2 FOREIGN KEY(city_province) REFERENCES province(province_name);
#执行报错。错误代码： 1215 Cannot add foreign key constraint

#原因：在新增外键时，关联表（city）中的相关字段（city_province）必须为index

#解决方法：for_list表for_name字段新增索引
ALTER TABLE city ADD INDEX city_province_index(city_province);

#再次执行新增外键操作：
ALTER TABLE city  ADD CONSTRAINT city_province_fk2 FOREIGN KEY (city_province) REFERENCES province (province_name);

#执行成功

#添加外键注意事项：两字段的类型、约束、大小必须一致