An index is not being created as expected when running some CREATE TABLE sql code-CodePudding

The following create table sql code is not producing the expected results.

CREATE TABLE mentorships (
mentor_ID INT NOT NULL,
mentee_ID INT NOT NULL,
status VARCHAR(255) NOT NULL,
project VARCHAR(255) NOT NULL,
PRIMARY KEY (mentor_ID, mentee_ID, project),
CONSTRAINT fk1 FOREIGN KEY(mentor_ID) REFERENCES co_employees(id) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT fk2 FOREIGN KEY(mentee_ID) REFERENCES co_employees(id) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT mm_constraint UNIQUE(mentor_ID, mentee_ID));

After running the code, when I check the indexes for the new table in phpmyadmin, I expect to see an index for fk1 as well as the others listed in the screenshot below. But as you can see in the screenshot, there is no fk1 index showing up.

Any idea as to why the fk1 index is not showing up or why it hasn't been created?

CodePudding user response：

To clarify the points made in the comments above, here's what it says in the manual:

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

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)

The "referencing table" in your case is mentorships, the table in which you are defining the foreign keys.

This statement from the manual is consistent with the points Jon Armstrong was making: the primary key satisfies the index requirement for the foreign key on mentor_id because that's the first column in the primary key index. But it does not satisfy the index for the foreign key on mentee_id because that's not the first column. Therefore it had to create a new index only for mentee_id.