MySQL InnoDB: автоматическое приращение непервичных ключей

В механизме хранения MySQL InnoDB первичный ключ обычно увеличивается автоматически. Однако возможно автоматическое увеличение и непервичного ключа.

Постановка задачи

Схема базы данных включает две таблицы: «book_comments» и «book_comments_votes». В таблице «book_comments» есть столбец «comment_id», не являющийся первичным ключом, который необходимо автоматически увеличить для обеспечения согласованности с таблицей «book_comments_votes», которая содержит ссылку на «comment_id» в качестве первичного ключа.

Решение

Для автоматического увеличения непервичного ключа его необходимо объявить как индекс. Вот пример:

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;

Альтернативы и обсуждение

Хотя создание простого индекса на основе непервичного ключа является наиболее простым решением, существуют некоторые альтернативы:

• Сделать comment_id первичным ключом: Для этого потребуется создать дополнительный уникальный индекс (book_id, timestamp, user_id) для обеспечения целостности.
• Сохранение всей информации первичный ключ в book_comments_votes: Это значительно увеличивает размер таблицы.

Однако рекомендуемым решением остается создание уникального индекса для непервичного ключа и избежание недостатков альтернатив. Этот подход обеспечивает простоту и целостность без ущерба для производительности или эффективности хранения данных.