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

CodePudding user response：

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)