MySQL InnoDB : auto-incrémentation des clés non primaires

Dans le moteur de stockage InnoDB de MySQL, la clé primaire est généralement auto-incrémentée. Cependant, il est également possible d'incrémenter automatiquement une clé non primaire.

L'énoncé du problème

Un schéma de base de données comprend deux tables : "book_comments" et "book_comments_votes". La table "book_comments" comporte une colonne de clé non primaire "comment_id" qui doit être auto-incrémentée pour des raisons de cohérence avec la table "book_comments_votes", qui contient une référence à "comment_id" comme clé primaire.

Une solution

Pour auto-incrémenter une clé non primaire, elle doit être déclarée comme index. Voici un exemple :

CREATE TABLE `book_comments` (
  `book_id` MEDIUMINT NOT NULL,
  `timestamp` MEDIUMINT NOT NULL,
  `user_id` MEDIUMINT NOT NULL,
  `vote_up` SMALLINT,
  `vote_down` SMALLINT,
  `comment` TEXT,
  `comment_id` MEDIUMINT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`book_id`, `timestamp`, `user_id`),
  KEY `comment_id` (`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Alternatives et discussion

Bien que la création d'un index simple sur la clé non primaire soit la solution la plus simple, certaines alternatives existent :

• Faire de comment_id la clé primaire : Cela nécessite la création d'un index unique supplémentaire sur (book_id, timestamp, user_id) pour maintenir l'intégrité.
• Stockage de l'intégralité de la clé primaire dans book_comments_votes : Cela augmente considérablement la taille de la table.

Cependant, le La solution recommandée reste de créer un index unique sur la clé non primaire et d'éviter les inconvénients des alternatives. Cette approche offre à la fois simplicité et intégrité sans sacrifier les performances ou l'efficacité du stockage des données.