MySQL InnoDB: autoincrement non-primary key

Question

Is it possible to auto-increment a non-primary key?

Table "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  Primary key -> (book_id, timestamp, user_id) 

There will be no other indexes on this table. However, I would like to make the comment_id column autoincrement so that I can easily create another table:

Table "book_comments_votes"

comment_id  (medium_int) user_id     (medium_int)  Primary key -> (comment_id, user_id) 

Users would be able to vote only once per book comment. This table enforces this rule by the primary key.

Question:

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

---

Alternatives, Discussion.

I would like to do this for simplicity as explained above. The alternatives are not promising.

• Make the commnet_id PK and enforce integrity through a unique index on book_id, timestamp, user_id. In this case, I would create an additional index.
• Keep the PK and replace the comment_id in the book_comments_votes with the entire PK. This would more than triple the size of the table.

Suggestions? Thoughts?

Answer 1

Yes you can. You just need to make that column be an index.

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;   insert into test(   testID,  string ) values ( 1,     'Hello' );   insert into test(  testID,  string ) values ( 2,     'world' ); 

Will insert rows with auto-incrementing values for 'testInc'. However this is a really dumb thing to do.

You already said the right way to do it:

"Make the comment_id PK and enforce integrity through a unique index on book_id, timestamp, user_id."

That's exactly the way that you should be doing it. Not only does it provide you with a proper primary key key for the table which you will need for future queries, it also satisfies the principle of least astonishment.

Answer 2

You've existing table. If you already gave a primary key to comment_id and the only purpose is to set auto_increment. You can drop the primary key to that column. MySQL allows a column with any key property can access auto_increment. So better try an index or unique key to comment_id like below.

1.Remove Primary Key

ALTER TABLE `book_comments` MODIFY `comment_id` INT(5) NOT NULL;  ALTER TABLE `book_comments` DROP PRIMARY KEY ; 

2. Add AUTO_INCREMENT with UNIQUE_KEY property.

ALTER TABLE 'brand_keywords' CHANGE COLUMN 'comment_id' 'comment_id' INT(5) NOT NULL AUTO_INCREMENT UNIQUE;