I know MySQL limits auto_increment columns to primary keys. Why is this? My first thought is that it's a performance restriction, since there probably is some counter table somewhere that must be locked in order to get this value.
Why can't I have multiple auto_increment columns in the same table?
Thanks.
Best Answer
Why would you want to have an auto_increment column that is not the primary key?
If you want a column to be an auto_increment, by definition, you are not storing meaningful data in that column. The only case where storing non-meaningful information makes sense is the special case that you want to have a synthetic primary key. In that case, the lack of information is a benefit because there is no risk that someone will ever come along in the future and want to change the data because some attribute of some entity changed.
Having multiple auto_increment columns in the same table seems even odder. The two columns would have the same data-- they're being generated by the same algorithm and being populated at the same time after all. I suppose you could come up with an implementation where it is possible for them to be slightly out of sync if there were enough concurrent sessions. But I can't imagine how that would ever be useful in an application.