'MariaDB foreign key auto generated index not created for the first column of PK

i'm facing a question without answer. I can't understand why the auto generated index from the FK creation is not working when the column seems to be the first one of PK, what i mean :

Create a simple schema with :

CREATE TABLE cat (name VARCHAR(255) PRIMARY KEY);
CREATE TABLE dog (name VARCHAR(255) PRIMARY KEY);
CREATE TABLE cat_dog_couple
(
    cat_name VARCHAR(255),
    dog_name VARCHAR(255),
    PRIMARY KEY (cat_name, dog_name),
    CONSTRAINT fk__cat_dog_couple__cat_name FOREIGN KEY (cat_name) references cat(name),
    CONSTRAINT fk__cat_dog_couple__dog_name FOREIGN KEY (dog_name) references dog(name)
);

These indexes will be generated :

+----------------+------------+------------------------------+--------------+-------------+
|     Table      | Non_unique |           Key_name           | Seq_in_index | Column_name |
+----------------+------------+------------------------------+--------------+-------------+
| cat_dog_couple |         0  | PRIMARY                      |            1 | cat_name    |
| cat_dog_couple |         0  | PRIMARY                      |            2 | dog_name    |
| cat_dog_couple |         1  | fk__cat_dog_couple__dog_name |            1 | dog_name    |
+----------------+------------+------------------------------+--------------+-------------+

Screen show index

I don't really understand why the index fk__cat_dog_couple__cat_name is not created? Is it a bug ? A technical limitation ? A technical choice ?

Tested on MariaDB 10.4.x and 10.5.x.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source