代码之家 › 专栏 › 技术社区 › Palisand

在复合主键中使用时未创建MySQL外键

primary-key foreign-keys mysql

Palisand · 技术社区 · 8 年前

CREATE TABLE test
(
  user_id int unsigned not null,
  post_id int unsigned not null,
  primary key (user_id, post_id),
  foreign key test_user_id (user_id) references user (id),
  foreign key test_post_id (post_id) references post (id)
);

show index from test;

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY      |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          0 | PRIMARY      |            2 | post_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | test_post_id |            1 | post_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

添加 id 列作为主键生成:

show index from test;

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | test_user_id |            1 | user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test  |          1 | test_post_id |            1 | post_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

它显示索引 test_user_id 使用由外键约束中使用的列组成的复合主键时,不会创建。应该是这样吗?有没有办法克服这个问题? (MySQL 5.7)

1 回复 | 直到 8 年前

Bill Karwin 8 年前

创建外键约束时,必须为其创建索引。如有必要,创建外键将自动创建一个新索引(至少是因为MySQL的一些旧版本,如4.0或其他版本,但有时甚至 更老的 版本没有自动创建FK索引(IIRC)。

但是如果已经存在合适的索引,MySQL就不需要创建新的索引。InnoDB至少有那么聪明。

任何包含FK列作为索引最左侧子集的索引都将满足该要求。主键索引(即表的聚集索引)工作正常。