mysql创建外键约束时报ERROR 1005 (HY000)

2019独角兽企业重金招聘Python工程师标准>>>

 ALTER TABLE `logistics_express_user` ADD CONSTRAINT `Reflogistics_express_del_com` FOREIGN KEY (`expr_del_com_id`) REFERENCES `logistics_expr_del_com` (`id`);

两线表都存在，对应的两个字段类型都是一样的varchar(32)，但还是报错

ERROR 1005 (HY000): Can't create table 'logistics.#sql-d971_112e1' (errno: 150)

网上说要在建外键的那个列上建索引，于是建立索引：

CREATE INDEX index_m_e_edcid ON logistics_express_user (expr_del_com_id);

再执行建外键的操作，还是不行，用语句查看该索引是否确实建立

 show index from logistics_express_user;

+------------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

| Table                  | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+------------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

| logistics_express_user |          0 | PRIMARY         |            1 | id              | A         |           3 |     NULL | NULL   |      | BTREE      |         |

| logistics_express_user |          1 | index_m_e_edcid |            1 | expr_del_com_id | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |

+------------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

发现索确实是建了，证明不是没建索引的问题

网上还分析说有可能是该外键约束的名称“Reflogistics_express_del_com"有可能与现有的名称冲突了，于是使用语句查询

use INFORMATION_SCHEMA;

select * from KEY_COLUMN_USAGE where CONSTRAINT_NAME = 'Reflogistics_express_del_com';

mysql> select * from KEY_COLUMN_USAGE where CONSTRAINT_NAME = 'Reflogistics_express_del_com';

Empty set (0.00 sec)

发现没有与现有的约束名称冲突

与这个网友的问题是一样的，基本上他尝试守的我也尝试了，也是不行，不能确定是不是mysql自身的问题，因为在我本机电脑上执行这些语句是可以成功的（windows8 mysql5)

http://www.iteye.com/problems/103359

在网上找到了这篇文章：http://blog.csdn.net/yageeart/article/details/7962674

提示说有可能是主键和外键的字符编码不一致，也可能存储引擎不一样

于是用以下语句查询看是否两个表的字符编码不一致：

mysql> SELECT table_name, table_type, engine,TABLE_COLLATION FROM information_schema.tables where table_name='logistics_express_user' or table_name='logistics_expr_del_com';

+------------------------+------------+--------+-------------------+

| table_name             | table_type | engine | TABLE_COLLATION   |

+------------------------+------------+--------+-------------------+

| logistics_expr_del_com | BASE TABLE | InnoDB | latin1_swedish_ci |

| logistics_express_user | BASE TABLE | InnoDB | utf8_general_ci   |

+------------------------+------------+--------+-------------------+

2 rows in set (0.00 sec)

发现两张表的字符集果然不一样，一个是latin1_swedish_ci，一个是utf8_general_ci，于是尝试修改logistics_expr_del_com表的字符集

mysql> ALTER TABLE logistics_expr_del_com CONVERT TO CHARACTER SET utf8;

Query OK, 1 row affected (0.26 sec)

Records: 1  Duplicates: 0  Warnings: 0

修改成功！

接着再用创建外键约束：

mysql> ALTER TABLE `logistics_express_user` ADD CONSTRAINT `Reflogistics_express_del_com` FOREIGN KEY (`expr_del_com_id`) REFERENCES `logistics_expr_del_com` (`id`);

Query OK, 3 rows affected (0.27 sec)

Records: 3  Duplicates: 0  Warnings: 0

成功！

转载于:https://my.oschina.net/u/914897/blog/378595