It’s so common to set the auto_increment column to primary key, it has become common belief that it’s required. Not true; an auto_increment column can use any index type. It doesn’t even have to be unique:
CREATE TABLE testTable ( id INT AUTO_INCREMENT, INDEX(id) ) ENGINE=MyISAM; -- Works for InnoDB too. INSERT INTO testTable VALUES (1), (1), (NULL); SELECT * FROM testTable; +----+ | id | +----+ | 1 | | 1 | | 2 | +----+
Hi!
The funny thing is that nothing in the code requires that we have an index on autoincrements, this is just the way it happens to be setup.
Cheers,
-Brian
Comment by Brian Aker — August 31, 2006 @ 7:19 pm