[SOLVED] Why KEY has automatically created when I make fk constraint for field?

Issue

I am creating the table with this syntax:

CREATE TABLE movies_genres 
(
    id BIGINT AUTO_INCREMENT,
    movie_id INT NOT NULL,
    genre_id INT NOT NULL,
    
    PRIMARY KEY (id),
    CONSTRAINT `fk_movie_id` FOREIGN KEY (movie_id) REFERENCES movies(id),
    CONSTRAINT `fk_genre_id` FOREIGN KEY (genre_id) REFERENCES genres(id),
    CONSTRAINT unique_id_pair UNIQUE(movie_id, genre_id)
);

But then I look at the info about the table in MySQL Workbench I see:

CREATE TABLE `movies_genres` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `movie_id` int NOT NULL,
  `genre_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id_pair` (`movie_id`,`genre_id`),
  KEY `fk_genre_id` (`genre_id`),
  CONSTRAINT `fk_genre_id` FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`),
  CONSTRAINT `fk_movie_id` FOREIGN KEY (`movie_id`) REFERENCES `movies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Why this line of code has generated?

KEY `fk_genre_id` (`genre_id`)

Also I see that extra index was created that I didn’t order…

Screenshot with extra index

Solution

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

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)

Answered By – Bill Karwin

Answer Checked By – Mary Flores (BugsFixing Volunteer)