Why does MySQL generate a `key` statement in DDL for composite primary keys?

Question

I am using MySQL version 8.3.0.

I can create a "many to many" relationship between persons and teams tables with the following SQL statements:

create table persons (
 id bigint not null,
 last_name varchar(255) not null,
 first_name varchar(255),
 primary key (id)
);
create table teams (
 id bigint not null,
 team_name varchar(255) not null,
 primary key (id)
);
create table person_team (
 person_id bigint not null,
 team_id bigint not null,
 primary key (person_id, team_id),
 constraint fk_person_team_person foreign key (person_id) references persons(id),
 constraint fk_person_team_team foreign key (team_id) references teams(id)
);

So the person_team table has a composite primary key, and each of its columns is a foreign key to the tables it joins.

If I retrieve the DDL statement for person_team with show create table person_team;, it gives me the following SQL:

CREATE TABLE `person_team` (
 `person_id` bigint NOT NULL,
 `team_id` bigint NOT NULL,
 PRIMARY KEY (`person_id`,`team_id`),
 KEY `fk_person_team_team` (`team_id`),
 CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`),
 CONSTRAINT `fk_person_team_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

So it automatically adds a KEY instruction (row 5) with the name foreign key to the teams table.

Why that behavior? This extra KEY instruction sounds not needed to me. Why does it show a key for fk_person_team_team and not for fk_person_team_person? Is the initial statement wrong or incomplete? Is the auto-generated DDL correct? When I dump my database with mysqldump, I obtain the same code as the auto-generated DDL. Is it safer to modify this code for restoring the database?

Answer 1

The answer, as is sometimes the case, can be found in the documentation:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

(emphasis mine).

You have two referencing columns in person_team: person_id and team_id; the former is covered by the table's primary key, but the latter is not, because it's not in the first position in the PK, so an extra index is created for it.

• Flow: Thanks for pointing to the relevant documentation! I think that actually surprises me is that creating a primary key on more than one column does not result in implicitly creating indexes on each of them.
• mustaccio: The compound PK index is used to enforce uniqueness of the combination of columns, which separate indexes on each column cannot do.
• Barmar: @FloT There's an implicit index on any prefix of the composite key, because it's implemented as a B-tree. But there's no implicit index on all the columns by themselves.

Answer 2

The reason is this : The foreign key reference fk_person_team_person already saw an index that can satisfy for foreign key relationship. In your case, it was the primary key since the lead column is person_id.

As for the other foreign key reference, fk_person_team_team, there was no index present that could satisfy for foreign key relationship.

Let's prove this with the following code

DROP DATABASE IF EXISTS FKTEST;
CREATE DATABASE FKTEST;
USE FKTEST
create table persons (
 id bigint not null,
 last_name varchar(255) not null,
 first_name varchar(255),
 primary key (id)
);
create table teams (
 id bigint not null,
 team_name varchar(255) not null,
 primary key (id)
);
CREATE TABLE `person_team` (
 `person_id` bigint NOT NULL,
 `team_id` bigint NOT NULL,
 PRIMARY KEY (`person_id`,`team_id`)
) ENGINE=InnoDB;
ALTER TABLE `person_team`
ADD FOREIGN KEY `fk_person_team_person` (`person_id`) REFERENCES `persons` (`id`);
SHOW CREATE TABLE person_team\G
ALTER TABLE `person_team`
ADD FOREIGN KEY `fk_person_team_team` (`team_id`) REFERENCES `teams` (`id`);
SHOW CREATE TABLE person_team\G

This will create the person_team table without the FKs at first. I will then add the FK one at a time

Here is the result

MariaDB [FKTEST]> DROP DATABASE IF EXISTS FKTEST;
ATE DATABASE FKTEST;
USE FKTEST
Query OK, 3 rows affected (0.035 sec)
MariaDB [(none)]> CREATE DATABASE FKTEST;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> USE FKTEST
Database changed
MariaDB [FKTEST]> create table persons (
 -> id bigint not null,
last_na -> last_name varchar(255) not null,
 -> first_name varchar(255),
 -> primary key (id)
 -> );
Query OK, 0 rows affected (0.009 sec)
MariaDB [FKTEST]> create table teams (
 -> id bigint not null,
 -> team_name varchar(255) not null,
 -> primary key (id)
 -> );
Query OK, 0 rows affected (0.014 sec)
MariaDB [FKTEST]> CREATE TABLE `person_team` (
 -> `person_id` bigint NOT NULL,
 -> `team_id` bigint NOT NULL,
 -> PRIMARY KEY (`person_id`,`team_id`)
 -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.011 sec)
MariaDB [FKTEST]> ALTER TABLE `person_team` ADD FOREIGN KEY `fk_person_team_person` (`person_id`) REFERENCES `persons` (`id`);
Query OK, 0 rows affected (0.034 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [FKTEST]> SHOW CREATE TABLE person_team\G
*************************** 1. row ***************************
 Table: person_team
Create Table: CREATE TABLE `person_team` (
 `person_id` bigint(20) NOT NULL,
 `team_id` bigint(20) NOT NULL,
 PRIMARY KEY (`person_id`,`team_id`),
 CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.002 sec)
MariaDB [FKTEST]> ALTER TABLE `person_team` ADD FOREIGN KEY `fk_person_team_team` (`team_id`) REFERENCES `teams` (`id`);
Query OK, 0 rows affected (0.036 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [FKTEST]> SHOW CREATE TABLE person_team\G
*************************** 1. row ***************************
 Table: person_team
Create Table: CREATE TABLE `person_team` (
 `person_id` bigint(20) NOT NULL,
 `team_id` bigint(20) NOT NULL,
 PRIMARY KEY (`person_id`,`team_id`),
 KEY `fk_person_team_team` (`team_id`),
 CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`),
 CONSTRAINT `fk_person_team_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.013 sec)
MariaDB [FKTEST]>

Please note the following

• The first FK Constraint was added but did not create an additional index
• The second FK Constraint was added and did create an additional index