MySQL InnoDB: Autoincrementing Non-Primary Keys

In MySQL's InnoDB storage engine, the primary key is typically auto-incremented. However, it is possible to auto-increment a non-primary key as well.

The Problem Statement

A database schema includes two tables: "book_comments" and "book_comments_votes." The "book_comments" table has a non-primary key column "comment_id" that needs to be auto-incremented for consistency with the "book_comments_votes" table, which contains a reference to "comment_id" as its primary key.

A Solution

To auto-increment a non-primary key, it must be declared as an index. Here's an example:

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 and Discussion

While creating a simple index on the non-primary key is the most straightforward solution, some alternatives exist:

• Making comment_id the primary key: This requires creating an additional unique index on (book_id, timestamp, user_id) to maintain integrity.
• Storing the entire primary key in book_comments_votes: This significantly increases the table size.

However, the recommended solution remains to create a unique index on the non-primary key and avoid the downsides of the alternatives. This approach provides both simplicity and integrity without sacrificing performance or data storage efficiency.