CODEWITHSUNDEEP
mysqlforeign-keysprimary-key
Palisand
Palisand

Reputation: 1362

MySQL foreign key not created when used within composite primary key

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      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Adding an id column as a primary key yields:

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      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

It appears the index test_user_id is not being created when using a composite primary key consisting of columns that are used within foreign key constraints. Should this be the case? Is there a way to overcome this? (MySQL 5.7)

Upvotes: 2

Views: 142

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

When you create a FOREIGN KEY constraint, there must be an index for it. If necessary, creating the FOREIGN KEY will automatically create a new index (at least since some old version of MySQL like 4.0 or something, but at one time, even older versions didn't create FK indexes automatically IIRC).

But if a suitable index already exists, MySQL doesn't need to create a new index. InnoDB is at least that smart.

Any index that includes the FK column as a left-most subset of the index will satisfy the requirement. The PRIMARY KEY index (i.e. the clustered index of the table) works fine.

Upvotes: 3

Related Questions