MySQL InnoDB: claves no primarias autoincrementales

En el motor de almacenamiento InnoDB de MySQL, la clave principal generalmente se incrementa automáticamente. Sin embargo, también es posible incrementar automáticamente una clave no principal.

El planteamiento del problema

Un esquema de base de datos incluye dos tablas: "book_comments" y "book_comments_votes". La tabla "book_comments" tiene una columna de clave no principal "comment_id" que debe incrementarse automáticamente para mantener la coherencia con la tabla "book_comments_votes", que contiene una referencia a "comment_id" como clave principal.

Una solución

Para incrementar automáticamente una clave no principal, debe declararse como un índice. Aquí hay un ejemplo:

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;

Alternativas y discusión

Si bien crear un índice simple en la clave no principal es la solución más sencilla, existen algunas alternativas:

• Hacer de comment_id la clave principal: Esto requiere crear un índice único adicional en (book_id, timestamp, user_id) para mantener la integridad.
• Almacenar el archivo completo clave principal en book_comments_votes: Esto aumenta significativamente el tamaño de la tabla.

Sin embargo, la solución recomendada sigue siendo crear un índice único en la clave no principal y evitar las desventajas de las alternativas. Este enfoque proporciona simplicidad e integridad sin sacrificar el rendimiento ni la eficiencia del almacenamiento de datos.