mysql auto_increment with multi valued key
a few weeks ago i had to create a mysql database where a table had a primary key which consisted of a foreign key and an index. i did a first try with a table like this:
CREATE TABLE testTable (
ref bigint not null,
idx bigint not null auto_increment,
PRIMARY KEY (ref, idx)
) ENGINE=MyISAM;
ref was a reference to another table so that was given and idx was to differentiate between versions. one requirement was that idx should start for each different ref with 1 and count up. but i forgot about that requirement and did it like above. i remembered the forgotten requirement a few days later when the database was already in use. i was quite surprised when i saw that all worked properly, like it should.
i expected that idx was different for every single row, but, it was only different for rows with the same ref. a quite an interesting behavior for an auto_increment column i thought and consulted the mysql manual. i found out that if an auto_increment column is a secondary value of an index the behavior is like this. if the last row of such a table is deleted it is reused later. but it works only if the auto_increment column is not the primary column in another index. in this case this index would generate the auto_increment values and it would be unique over all rows.