Is it possible to have an AUTO_INCREMENT column that permits duplicates?

Question

I have a table that has an AUTO_INCREMENT field. Currently, it is also a PRIMARY KEY.

However, there are situations where I need this AUTO_INCREMENT column to permit duplicates. In other words - two different rows can have the same value inside the AUTO_INCREMENT column. This would mean having an AUTO_INCREMENT field that is not a PRIMARY KEY.

Is this possible?

I'm guessing it's not, since whenever I try to do it, I get this error:

ERROR 1075 (42000) at line 130: Incorrect table definition; there can be only one auto column and it must be defined as a key

I like to have the AUTO_INCREMENT field because it saves me from having to manually store / increment a separate counter elsewhere in my database. I can just insert into the table and grab the value that was inserted. However, if I can't have duplicates, it seems like I'm going to be stuck with using a separate table to track and manually increment this field.

UPDATE: As a quick clarification, I am already familiar with grouping the AUTO_INCREMENT field with another key, as described here. Let's assume for the sake of argument that this solution won't work due to other constraints in the database.

This question is very similar to http://stackoverflow.com/questions/634231/can-you-use-auto-increment-in-mysql-with-out-it-being-the-primary-key — monowerker, May 28 '09 at 17:58
In my specific case, the field represents the ID of a subtask that is part of a larger task. In most cases, the ID needs to be globally unique -- but in some cases, duplicates must be allowed if the same subtask is applied multiple times from different tasks. — Runcible, May 28 '09 at 17:58
Sounds like no need a self-reference in the table for the subtasks to reference their supertasks. — Oliver Friedrich, May 28 '09 at 18:14

score 5 · Accepted Answer · answered May 28 '09 at 18:07

An auto-increment field in MySQL must be part of a key (i.e. an index), but not necessarily part of a primary key or unique key.

CREATE TABLE mytable (
  id   INT PRIMARY KEY,
  otto INT AUTO_INCREMENT,
  KEY (otto)
);

-- allow the auto-increment to generate a value

INSERT INTO mytable (id, otto) VALUES (123, DEFAULT);

SELECT * FROM mytable;

> 123, 1

-- specify a duplicate value, overriding the auto-increment mechanism

INSERT INTO mytable (id, otto) VALUES (456, 1); 

SELECT * FROM mytable;

> 123, 1
> 456, 1

-- allow the auto-increment to generate another value

INSERT INTO mytable (id, otto) VALUES (789, DEFAULT);

SELECT * FROM mytable;

> 123, 1
> 456, 1
> 789, 2

score 2 · Answer 2 · answered May 28 '09 at 18:02

Sounds like 'subtask' is a table to which 'task' has a FK reference to. That is, if subtasks are reused.

OTOH if a task can have many subtasks, and a subtask can be linked to more than one task then you're looking at many-to-many in a seperate table.

in either case I don't think you want the DB autogenerating these 'linked-IDs'.

Is it possible to have an AUTO_INCREMENT column that permits duplicates?

2 Answers2