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

Question

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?

The primary key satisfies the index requirement (index on mentor_id), so an additional explicit index on mentor_id is not created. — Jon Armstrong
– Jon Armstrong, Commented Nov 1, 2022 at 1:58
From dataedo.com/kb/tools/phpmyadmin/view-table-foreign-keys: To see FKs of a table first select table from the object explorer, then go to Structure tab and then select Relation view. Please note that in different versions it might be in different locations. — Barmar
– Barmar, Commented Nov 1, 2022 at 2:01
Notice in the second set of tables in the following fiddle, that the PK no longer contains mentor_id (I removed it), so the index fk1b is automatically created to satisfy the requirement of the foreign key constraint. dbfiddle.uk/5b_pWQJA — Jon Armstrong
– Jon Armstrong, Commented Nov 1, 2022 at 2:12
@JonArmstrong, since the primary also consists of mentee_id, wouldn't that primary key also satisfy the index requirement (index on mentee-id)? If so, then wouldn't this result in an explicit index not being created on mentee-id either? Which would result in fk2 not being listed in indexes. However, fk2 is listed in indexes. — Eddie
– Eddie, Commented Nov 1, 2022 at 2:15
No. Only the leading prefix columns of the PK can be used to satisfy that requirement. mentee_id is not the leading prefix of the PK. If that FK were the composite (mentor_id, mentee_id), then the PK would satisfy the requirement, since those columns are the leading prefix and in the right order. — Jon Armstrong
– Jon Armstrong, Commented Nov 1, 2022 at 2:18

Bill Karwin · Accepted Answer · 2022-11-01 02:58:21Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related