'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 |
+----------------+------------+------------------------------+--------------+-------------+
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 |
---|