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.

Answer 1

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'.

Answer 2

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