MySQL InnoDB: autoincrement non-primary key

by

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.

More Related Contents:

  1. Is there a REAL performance difference between INT and VARCHAR primary keys?
  2. How to debug Lock wait timeout exceeded on MySQL?
  3. 1114 (HY000): The table is full
  4. difference between primary key and unique key
  5. How to shrink/purge ibdata1 file in MySQL
  6. Insert auto increment primary key to existing table
  7. MySql: MyISAM vs. Inno DB! [closed]
  8. How to convert all tables from MyISAM into InnoDB?
  9. UUID performance in MySQL?
  10. MySQL InnoDB foreign key between different databases
  11. ALTER TABLE to add a composite primary key
  12. Force InnoDB to recheck foreign keys on a table/tables?
  13. What is MySQL’s default ON DELETE behavior?
  14. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  15. How do I repair an InnoDB table?
  16. How to enable INNODB in mysql
  17. Is there any performance gain in indexing a boolean field?
  18. How to disable index in innodb
  19. Why does MySQL autoincrement increase on failed inserts?
  20. How can I check MySQL engine type for a specific table?
  21. How do I rename a MySQL database (change schema name)?
  22. Strings as Primary Keys in MYSQL Database [closed]
  23. How to regex in a MySQL query
  24. MySQL Integer vs DateTime index
  25. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  26. How do I lock on an InnoDB row that doesn’t exist yet?
  27. MySQL DROP all tables, ignoring foreign keys
  28. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  29. Composite Primary Key performance drawback in MySQL
  30. How can I rebuild indexes and update stats in MySQL innoDB?

Leave a Comment