How to Auto-Increment Non-Primary Keys in MySQL InnoDB?

MySQL InnoDB: Auto-Incrementing Non-Primary Keys

MySQL's InnoDB storage engine allows auto-incrementing values for primary keys. However, auto-incrementing non-primary keys is not directly supported.

Specific Question: Table Structure

Consider the following table structure for "book_comments":

book_id     medium_int
timestamp   medium_int
user_id     medium_int
vote_up     small_int
vote_down   small_int
comment     text
comment_id  medium_int

Is Auto-Incrementing Non-Primary Keys Possible?

Yes, it is possible to auto-increment a non-primary key. However, it requires creating an index on the column to be auto-incremented.

Recommended Solution

Despite the possibility of auto-incrementing non-primary keys, the recommended solution is to make "comment_id" the primary key and create a unique index on the combination of "book_id", "timestamp", and "user_id" for data integrity purposes.

Additional Considerations

The alternative suggestions mentioned in the question have drawbacks:

• Making "comment_id" the primary key and creating a unique index: This solution introduces an additional index, which may not be desirable.
• Replacing "comment_id" with the primary key in "book_comments_votes": This significantly increases the size of "book_comments_votes" and can affect performance.

Therefore, the most efficient and robust solution is to follow the recommended approach of using "comment_id" as the primary key and creating a unique index on "book_id", "timestamp", and "user_id".

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