MySQL -- créer deux index, un seul apparaît dans la sortie de SHOW CREATE TABLE

Question

J'ai une table de liaison many-to-many et j'essaie de mettre en place deux clés étrangères sur celle-ci. J'exécute ces deux instructions :

ALTER TABLE address_list_memberships
ADD CONSTRAINT fk_address_list_memberships_address_id
FOREIGN KEY index_address_id (address_id)
REFERENCES addresses (id);

ALTER TABLE address_list_memberships
ADD CONSTRAINT fk_address_list_memberships_list_id
FOREIGN KEY index_list_id (list_id)
REFERENCES lists (id);

Je m'attendrais à ce que lorsque j'exécute SHOW CREATE TABLE address_list_memberships Je verrais bien ça :

[...]
KEY `index_address_id` (`address_id`),
KEY `index_list_id` (`list_id`),
CONSTRAINT `fk_address_list_memberships_list_id` FOREIGN KEY (`list_id`) 
    REFERENCES `lists` (`id`),
CONSTRAINT `fk_address_list_memberships_address_id` FOREIGN KEY (`address_id`) 
    REFERENCES `addresses` (`id`)

Mais à la place, j'obtiens ceci :

[...]
KEY `index_list_id` (`list_id`),
CONSTRAINT `fk_address_list_memberships_list_id` FOREIGN KEY (`list_id`) 
    REFERENCES `lists` (`id`),
CONSTRAINT `fk_address_list_memberships_address_id` FOREIGN KEY (`address_id`) 
    REFERENCES `addresses` (`id`)

Il semble qu'il n'y ait qu'un seul indice. Cela semble contredire la Documentation sur MySQL qui disent que MySQL crée automatiquement un index sur la colonne de référencement chaque fois que vous créez une clé étrangère.

J'ai remarqué ce problème d'index unique chaque fois que je crée deux FK sur une table, que j'utilise un outil graphique tel que CocoaMySQL ou SQLyog, ou que je le fasse en ligne de commande.

Tout éclaircissement de ce mystère serait très apprécié.

Answer 1

Je viens de l'essayer et ça marche bien pour moi. J'ai copié et collé le ALTER les déclarations que vous avez écrites et voici ce que j'obtiens :

mysql> show create table address_list_memberships;

CREATE TABLE `address_list_memberships` (
  `address_id` bigint(20) unsigned NOT NULL,
  `list_id` bigint(20) unsigned NOT NULL,
  KEY `index_address_id` (`address_id`),
  KEY `index_list_id` (`list_id`),
  CONSTRAINT `fk_address_list_memberships_list_id` 
    FOREIGN KEY (`list_id`) REFERENCES `lists` (`id`),
  CONSTRAINT `fk_address_list_memberships_address_id` 
    FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

J'utilise MySQL 5.0.51a sur Mac OS X.

éditer : Essayez la requête suivante pour obtenir tous les index MySQL pense existent sur votre table :

SELECT * FROM information_schema.key_column_usage 
WHERE table_schema = 'test' AND table_name = 'address_list_memberships'\G

(J'ai utilisé la base de données 'test' pour mon test ; vous devez remplacer cette chaîne par le nom du schéma où votre table est définie).

Answer 2

Cela n'a pas vraiment d'importance. Vous avez toujours un index sur list_id. MySQL exige que toute contrainte de clé étrangère ait également un index sur les champs de référence. Comme index_list_id et fk_address_list_memberships_list_id sont tous deux construits sur list_id, MySQL le voit probablement et utilise index_list_id comme index, en le renommant en fk_address_list_memberships_list_id. Vous pouvez même éviter de déclarer l'index, puisque MySQL le fera implicitement dans la version que vous utilisez.