文章内容
2019/11/30 16:51:35,作 者: 黄兵
[HY000][1215] Cannot add foreign key constraint.
最近在使用SQLAlchemy建库的时候,出现了如下错误:
[HY000][1215] Cannot add foreign key constraint.
错误截图如下:
说实在的这些信息不能提供任何帮助。只能自己慢慢排查。
首先查看了数据类型是否一直,结果是一样的,具体截图如下:
User表id数据类型是varchar(64):
另外一张表数据类型也是varchar(64):
这个排除了,只能看看两张表的编码方式是否一致,执行如下两个命令:
show create table users; --结果如下: CREATE TABLE `users` ( `id` varchar(64) NOT NULL, `email` varchar(64) DEFAULT NULL, `familyName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `givenName` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `Timestamp` datetime DEFAULT NULL, `role_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `ix_users_email` (`email`), KEY `ix_users_role_id` (`role_id`), CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
可以看到Users表的编码格式是latin1,我们再看看另外一张表的编码方式:
SHOW CREATE TABLE private_number_phone_number; --结果如下: CREATE TABLE `private_number_phone_number` ( `id` int(11) NOT NULL AUTO_INCREMENT, `phone_number` varchar(32) DEFAULT NULL, `phone_number_section_id` int(11) DEFAULT NULL, `time_create` datetime DEFAULT NULL, `time_expired` datetime DEFAULT NULL, `phone_number_status_id` int(11) DEFAULT NULL, `country_id` int(11) DEFAULT NULL, `user_id` varchar(64) NOT NULL, PRIMARY KEY (`id`), KEY `phone_number_section_id` (`phone_number_section_id`), KEY `country_id` (`country_id`), KEY `phone_number_status_id` (`phone_number_status_id`), KEY `private_number_phone_number_user_id_index` (`user_id`), CONSTRAINT `private_number_phone_number_ibfk_1` FOREIGN KEY (`phone_number_section_id`) REFERENCES `private_number_property_value` (`id`), CONSTRAINT `private_number_phone_number_ibfk_3` FOREIGN KEY (`country_id`) REFERENCES `user_phone_number_country` (`id`), CONSTRAINT `private_number_phone_number_ibfk_4` FOREIGN KEY (`phone_number_status_id`) REFERENCES `user_phone_number_status` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
可以看到编码方式是utf8bm4,两张表的编码方式不同。所以需要修改一下这张表的编码方式,具体SQL如下:
ALTER TABLE private_number_phone_number CONVERT TO CHARACTER SET latin1;
之后再次修改user_id的外键,执行SQL如下:
alter table private_number_phone_number add constraint private_number_phone_number_users_id_fk foreign key (user_id) references users (id);
执行成功,没有任何错误。
参考资料:
1、MySQL Error Code 1215: “Cannot add foreign key constraint”
3、MySQL modify charset to table
黄兵个人博客原创。
转载请著名出处:黄兵个人博客 - [HY000][1215] Cannot add foreign key constraint.
评论列表