创建外键时mysql errno 150

Question

我收到 errno 150，但不明白原因。我读过一百万个其他答案，说你必须在父列上设置索引，我这样做了，但我不确定为什么我仍然收到此错误。

父表索引:

mysql> show indexes from plans;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| plans |          0 | PRIMARY        |            1 | plan_id     | A         |          86 |     NULL | NULL   |      | BTREE      |         |               |
| plans |          0 | plan_id_UNIQUE |            1 | plan_id     | A         |          86 |     NULL | NULL   |      | BTREE      |         |               |
| plans |          1 | plan_id        |            1 | plan_id     | A         |          86 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.08 sec)

子表索引:

mysql> show indexes from promos;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| promos |          0 | PRIMARY  |            1 | promo_id    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| promos |          1 | plan_id  |            1 | plan_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.06 sec)

错误:

mysql> alter table promos add foreign key (plan_id) references plans (plan_id) on delete cascade on update cascade;
ERROR 1005 (HY000): Can't create table 'dev.#sql-2ce_599a' (errno: 150)

Answer 1

两列必须具有相同的排序规则和字符集。

这有效:http://sqlfiddle.com/#!2/0f917