MySQL: Why is auto_increment limited to just primary keys?

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.

In fact the AUTO_INCREMENT attribute is not limited to the PRIMARY KEY (any more). It used to be so in old versions - definitely 3.23 and probably 4.0. Still the MySQL manual for all versions since 4.1 reads like this


There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.

So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key. If that makes sense, is a different topic though.

I should also mention that an AUTO_INCREMENT column should always be an integer type (technically a floating point type is also allowed) and that it should be UNSIGNED. A SIGNED type not only wastes half the key space, it can also lead to huge problems if a negative value is inserted by accident.

Finally MySQL 4.1 and later defines a type alias SERIAL for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

StackExchange Database Administrators Q#3467, answer score: 13