Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I just noticed I forgot to use the @pop in the transactions. I retried the example and posted it at the bottom of my answer !!! –  RolandoMySQLDBA Jun 24 '11 at 1:08
    
I like this question because it got me thinking out of the box with MySQL which I don't do very much on a Friday night. +1 !!! –  RolandoMySQLDBA Jun 24 '11 at 1:10

4 Answers 4

up vote 5 down vote accepted

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.

share|improve this answer
    
+1 for straightforward database common sense !!! –  RolandoMySQLDBA Jun 24 '11 at 0:42
    
It was more of a theoretical question -- I have no practical use for having multiple auto_increment columns, I just wanted to hear peoples' explanations as to why it wasn't possible. Thanks for taking the time to answer! :) –  Christopher Armstrong Jun 24 '11 at 1:20
1  
"Why would you want to have an auto_increment column that is not the primary key?" -- I can think of a few reasons, personally. But that's OT. :-) –  Denis Jun 24 '11 at 4:49
    
I'm doing something like this now - and it's not meaningless data. I need to know a count of all records ever inserted into a table, but have more useful primary keys already. This solves that, as every new record has a value of which it is. A (weak) analogy would be the "you're our 10,000th customer" requirement. Customers get deleted over time, so COUNT(*) isn't valid. –  Cylindric Feb 15 '13 at 12:21
    
Why would you want to have an auto_increment column that is not the primary key? –  phil_w Nov 4 at 18:50

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.

share|improve this answer
    
+1 for the fact that auto_increment isn't limited to PKs. Not sure why you think using negative numbers for a surrogate key leads to "huge problems" though. –  sqlvogel Jun 29 '11 at 8:25

This is an interesting question because different databases have unique approaches for providing auto_increment.

MySQL : Only one auto_increment key is generated to uniquely identify a row in a table. There is not a lot of explanation behind why, but just implementation. Depending on datatype, auto_increment values are fixed by the length of datatype in bytes:

  • Max TINYINT is 127
  • Max UNSIGNED TINTINT is 255
  • Max INT is 2147483647
  • Max UNSIGNED INT is 4294967295

PostgreSQL

The internal datatype serial is used for auto increment from 1 to 2,147,483,647. Larger ranges are allowed using bigserial.

Oracle : The schema object called SEQUENCE can create new numbers by simply summoning the nextval function. PostgreSQL also has such a mechanism.

Here is a nice URL that provides how other DBs specify them : http://www.w3schools.com/sql/sql_autoincrement.asp

Now concerning your question, if you really want to have multiple auto_increment columns in a single table, you will have to emulate that.

Two reasons why you must emulate this:

  1. MySQL accommodates only one increment column per table as does PostgreSQL, Oracle, SQL Server, and MS Access.
  2. MySQL does not have a SEQUENCE schema object like Oracle and PostgreSQL.

How would you emulate it ???

Using multiple tables that have only one auto_increment column and mapping them to the desired columns in target tables. Here is an example:

Copy and Paste This Example:

use test
DROP TABLE IF EXISTS teacher_popquizzes;
CREATE TABLE teacher_popquizzes
(
    teacher varchar(20) not null,
    class varchar(20) not null,
    pop_mon INT NOT NULL DEFAULT 0,
    pop_tue INT NOT NULL DEFAULT 0,
    pop_wed INT NOT NULL DEFAULT 0,
    pop_thu INT NOT NULL DEFAULT 0,
    pop_fri INT NOT NULL DEFAULT 0,
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
INSERT INTO teacher_popquizzes (teacher,class) VALUES
('mr jackson','literature'),
('mrs andrews','history'),
('miss carroll','spelling');
DROP TABLE IF EXISTS mon_seq;
DROP TABLE IF EXISTS tue_seq;
DROP TABLE IF EXISTS wed_seq;
DROP TABLE IF EXISTS thu_seq;
DROP TABLE IF EXISTS fri_seq;
CREATE TABLE mon_seq
(
    val INT NOT NULL DEFAULT 0,
    nextval INT NOT NULL DEFAULT 1,
    PRIMARY KEY (val)
);
CREATE TABLE tue_seq LIKE mon_seq;
CREATE TABLE wed_seq LIKE mon_seq;
CREATE TABLE thu_seq LIKE mon_seq;
CREATE TABLE fri_seq LIKE mon_seq;
BEGIN;
INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
SELECT nextval INTO @pop FROM mon_seq;
UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 2;
COMMIT;
BEGIN;
INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
SELECT nextval INTO @pop FROM tue_seq;
UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 1;
COMMIT;
BEGIN;
INSERT INTO wed_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
SELECT nextval INTO @pop FROM wed_seq;
UPDATE teacher_popquizzes SET pop_wed = pop_wed + 1 WHERE id = 2;
COMMIT;
SELECT * FROM teacher_popquizzes;

This will create a table of pop quizzes for teachers. I also created five sequence emulators, one for each day of the school week. Each sequence emulator works by inserting the value 0 in the val column. If the sequence emulator is empty, it starts with val 0, nextval 1. If not, the nextval column is incremented. You can then fetch the nextval column from the sequence emulator.

Here are the sample results from the example:

mysql> CREATE TABLE teacher_popquizzes
    -> (
    ->     teacher varchar(20) not null,
    ->     class varchar(20) not null,
    ->     pop_mon INT NOT NULL DEFAULT 0,
    ->     pop_tue INT NOT NULL DEFAULT 0,
    ->     pop_wed INT NOT NULL DEFAULT 0,
    ->     pop_thu INT NOT NULL DEFAULT 0,
    ->     pop_fri INT NOT NULL DEFAULT 0,
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO teacher_popquizzes (teacher,class) VALUES
    -> ('mr jackson','literature'),
    -> ('mrs andrews','history'),
    -> ('miss carroll','spelling');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS mon_seq;
Query OK, 0 rows affected (0.06 sec)

mysql> DROP TABLE IF EXISTS tue_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS wed_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS thu_seq;
Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE IF EXISTS fri_seq;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE mon_seq
    -> (
    ->     val INT NOT NULL DEFAULT 0,
    ->     nextval INT NOT NULL DEFAULT 1,
    ->     PRIMARY KEY (val)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE tue_seq LIKE mon_seq;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE wed_seq LIKE mon_seq;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE thu_seq LIKE mon_seq;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE fri_seq LIKE mon_seq;
Query OK, 0 rows affected (0.14 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM mon_seq;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM tue_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO wed_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM wed_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_wed = pop_wed + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM teacher_popquizzes;
+--------------+------------+---------+---------+---------+---------+---------+----+
| teacher      | class      | pop_mon | pop_tue | pop_wed | pop_thu | pop_fri | id |
+--------------+------------+---------+---------+---------+---------+---------+----+
| mr jackson   | literature |       0 |       1 |       0 |       0 |       0 |  1 |
| mrs andrews  | history    |       0 |       1 |       1 |       0 |       0 |  2 |
| miss carroll | spelling   |       0 |       0 |       0 |       0 |       0 |  3 |
+--------------+------------+---------+---------+---------+---------+---------+----+
3 rows in set (0.00 sec)

mysql>

If you really need multiple auto increment values in MySQL, this is the closest way to emulate it.

Give it a Try !!!

UPDATE 2011-06-23 21:05

I just noticed in my example I dont use the @pop value.

This time I replaced 'pop_tue = pop_tue + 1' with 'pop_tue = @pop' and retried the example:

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS teacher_popquizzes;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE teacher_popquizzes
    -> (
    ->     teacher varchar(20) not null,
    ->     class varchar(20) not null,
    ->     pop_mon INT NOT NULL DEFAULT 0,
    ->     pop_tue INT NOT NULL DEFAULT 0,
    ->     pop_wed INT NOT NULL DEFAULT 0,
    ->     pop_thu INT NOT NULL DEFAULT 0,
    ->     pop_fri INT NOT NULL DEFAULT 0,
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO teacher_popquizzes (teacher,class) VALUES
    -> ('mr jackson','literature'),
    -> ('mrs andrews','history'),
    -> ('miss carroll','spelling');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS mon_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS tue_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS wed_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS thu_seq;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS fri_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE mon_seq
    -> (
    ->     val INT NOT NULL DEFAULT 0,
    ->     nextval INT NOT NULL DEFAULT 1,
    ->     PRIMARY KEY (val)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE tue_seq LIKE mon_seq;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE wed_seq LIKE mon_seq;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE thu_seq LIKE mon_seq;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE fri_seq LIKE mon_seq;
Query OK, 0 rows affected (0.08 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT nextval INTO @pop FROM tue_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = @pop WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;

Query OK, 2 rows affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM tue_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = @pop WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO wed_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT nextval INTO @pop FROM wed_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_wed = @pop WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM teacher_popquizzes;
+--------------+------------+---------+---------+---------+---------+---------+----+
| teacher      | class      | pop_mon | pop_tue | pop_wed | pop_thu | pop_fri | id |
+--------------+------------+---------+---------+---------+---------+---------+----+
| mr jackson   | literature |       0 |       2 |       0 |       0 |       0 |  1 |
| mrs andrews  | history    |       0 |       1 |       1 |       0 |       0 |  2 |
| miss carroll | spelling   |       0 |       0 |       0 |       0 |       0 |  3 |
+--------------+------------+---------+---------+---------+---------+---------+----+
3 rows in set (0.00 sec)

mysql>
share|improve this answer
    
Cool example... thanks! –  Christopher Armstrong Jun 24 '11 at 1:22
    
Your summary is not completely correct: PostgreSQL supports any number of auto increment (serial) columns, just like Oracle does (in both cases the columns are filled with a value from a sequence). PostgreSQL also offers the bigserial data type which offers a range that is much larger than 2,147,483,647 –  a_horse_with_no_name Jun 29 '11 at 12:30
    
@a_horse_with_no_name : sorry for the oversight. I am still a journeyman with postgresql. I'll update my answer later. I am on the road answer from iPhone. Have a good day! –  RolandoMySQLDBA Jun 29 '11 at 12:37

As XL says, it isn't limited to just primary keys. It's a potential limitation that you can only have one such column per table but the best workaround is to generate as many numbers you need in another table and then insert them where you need to.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.