Mysql create constraint foreign key faild.trouble shooting method share

最新推荐文章于 2025-11-26 14:12:29 发布

转载最新推荐文章于 2025-11-26 14:12:29 发布 · 213 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dogharry/p/5169460.html

文章标签：

#数据库

本文详细解析了在MySQL中创建包含外键约束的表时遇到的错误1215，具体为无法添加外键约束的问题。通过SHOW ENGINE INNODB STATUS命令定位问题，并提供了错误信息解读，指出可能的原因包括引用表中未找到合适的索引，或者表和引用表的列类型不匹配。

mysql> create table tb_test (id int(10) not null auto_increment primary key,action_id int(10) not null,error_code int(10) not null default 0,desc_key varchar(64) not null default 'audit.log.default',INDEX(action_id),constraint `FK_ACTIONID` foreign key(`action_id`) references `tb_tab1` (`action_id`));
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql>

定位方法如下：

SHOW ENGINE INNODB STATUS:

LATEST FOREIGN KEY ERROR
------------------------
2016-01-29 08:45:15 7f5b11b09700 Error in foreign key constraint of table auditdb/tb_test:
foreign key(`action_id`) references `tb_tab1` (`action_id`)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

很详细

转载于:https://www.cnblogs.com/dogharry/p/5169460.html