Can a Non-Primary Key Column in MySQL InnoDB be Auto-Incremented?

Auto-Incrementing Non-Primary Keys in MySQL InnoDB

It is possible to auto-increment a non-primary key column in MySQL InnoDB. This can be achieved by creating an index on the column.

In the given example, to auto-increment the comment_id column in the book_comments table, follow these steps:

<code class="sql">CREATE TABLE book_comments (
    book_id mediumint,
    timestamp mediumint,
    user_id mediumint,
    vote_up smallint,
    vote_down smallint,
    comment text,
    comment_id mediumint,

    PRIMARY KEY (book_id, timestamp, user_id),
    INDEX id (comment_id)
);</code>

Adding the INDEX on comment_id enables the auto-increment behavior while ensuring that the corresponding index is maintained by InnoDB. The id index name can be customized as needed.

For the book_comments_votes table, it's recommended to use the entire primary key from book_comments as the foreign key to enforce referential integrity and avoid duplicates.

While making comment_id the primary key and enforcing integrity through a unique index on book_id, timestamp, and user_id is conceptually viable, it's generally considered best practice to have a unique primary key for each table. This ensures consistent and reliable data retrieval and maintenance.

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