MySQL -- 外键创建失败

使用show engine innodb status\G 查看数据库状态的时候，发现以下报错信息：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2017-09-16 16:22:38 7ff485c25700 Error in foreign key constraint of table abce/#sql-1f3_284b3:  foreign key (sappres_id) references s_app_res (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. ------------------------

可以看出，创建外键索引失败，在父表和子表之间约束不匹配。但是只能看出被参照的表是s_app_res，对应的字段是id。但是没有显示参照表的名字，只是显示参照表中的列名。

使用以下语句将参照表的信息找出来。其中，table_schema字段为db的名称（所属的数据库），字段table_name为表的名称。

?

1 2 3 4 5 6 7 8 9

mysql> select * from information_schema.columns where column_name='sappres_id'; +---------------+--------------+-------------------+-------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+ | table_catalog | table_schema | table_name        | column_name       | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_scale | datetime_precision | character_set_name | collation_name | column_type | column_key | extra | privileges                      | column_comment | +---------------+--------------+-------------------+-------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+ | def           | abce         | s_sync_res        | sappres_id        |               15 | null           | yes         | int       |                     null |                   null |                10 |             0 |               null | null               | null           | int(11)     |            |       | select,insert,update,references |                | +---------------+--------------+-------------------+-------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+ 1 row in set (0.04 sec)   mysql>

　　

然后对比表s_app_res、s_sync_res的定义语句：
s_app_res.id是表的主键，但是字段类型定义格式为：

?

1	`id` bigint(20) not null

s_sync_res.sappres_id 的定义格式为：

?

1	`sappres_id` int(11) DEFAULT NULL

　　

原因找到，修改这两个对应字段的定义即可。