Can I Auto-Increment a Non-Primary Key in MySQL InnoDB?

MySQL InnoDB: Auto-Incrementing Non-Primary Keys

In MySQL InnoDB, it is possible to auto-increment a non-primary key column, but doing so is generally not recommended.

Question:

Is it possible to auto-increment a non-primary key, specifically the comment_id column in the book_comments table?

Alternatives:

The questioner suggests two alternatives to using an auto-incrementing non-primary key:

• Making comment_id the primary key and enforcing integrity with a unique index on (book_id, timestamp, user_id)
• Keeping the existing primary key and replacing comment_id with the entire primary key in the book_comments_votes table

However, these alternatives have drawbacks such as adding unnecessary index overhead or significantly increasing table size.

Answer:

Yes, it is possible to auto-increment a non-primary key by creating an index on that column. The following example creates a table with an auto-incrementing testInc column:

<code class="sql">CREATE TABLE `test` (
  `testID` int(11) NOT NULL,
  `string` varchar(45) DEFAULT NULL,
  `testInc` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`testID`),
  KEY `testInc` (`testInc`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;</code>

However, experts strongly advise against this practice. A more appropriate approach is to make comment_id the primary key and enforce integrity through a unique index on (book_id, timestamp, user_id). This approach provides a proper primary key for the table and adheres to the principle of least astonishment.

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