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.