ProfileTwist ProfileTwist - 5 months ago 44
MySQL Question

Mysql Innodb: Autoincrement non-Primary Key

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

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.