MySQL InnoDB: incremento automático de chaves não primárias

No mecanismo de armazenamento InnoDB do MySQL, a chave primária normalmente é incrementada automaticamente. No entanto, também é possível incrementar automaticamente uma chave não primária.

A Declaração do Problema

Um esquema de banco de dados inclui duas tabelas: "book_comments" e "book_comments_votes". A tabela "book_comments" possui uma coluna de chave não primária "comment_id" que precisa ser incrementada automaticamente para consistência com a tabela "book_comments_votes", que contém uma referência a "comment_id" como sua chave primária.

Uma solução

Para incrementar automaticamente uma chave não primária, ela deve ser declarada como um índice. Aqui está um exemplo:

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 e discussão

Embora criar um índice simples na chave não primária seja a solução mais direta, existem algumas alternativas:

• Tornar comment_id a chave primária: Isso requer a criação de um índice exclusivo adicional em (book_id, timestamp, user_id) para manter a integridade.
• Armazenamento de todo o chave primária em book_comments_votes: Isso aumenta significativamente o tamanho da tabela.

No entanto, a solução recomendada continua sendo criar um índice exclusivo na chave não primária e evitar as desvantagens das alternativas. Essa abordagem oferece simplicidade e integridade sem sacrificar o desempenho ou a eficiência do armazenamento de dados.