Can You Auto-Increment Non-Primary Keys in MySQL InnoDB?

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:

<code class="mysql">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;</code>

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.

The above is the detailed content of Can You Auto-Increment Non-Primary Keys in MySQL InnoDB?. For more information, please follow other related articles on the PHP Chinese website!