MySQL auto-increment on a non-primary key

Question

I'd like to know if / how it's possible to make a second column auto-increment for each primary key:

CREATE TABLE test (
    `id` INTEGER UNSIGNED NOT NULL,
    `subId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`, `subId`)
)
ENGINE = InnoDB;

This creation, unfortunately, doesn't work, only if I specify ID as primary key and subId as index key (but I need them both together and ID can repeat).

Example data (what I need):

1, 1
1, 2
1, 3
2, 1
2, 2
3, 1

The problem with making ID primary and subId index is that subId will increment independently of ID.

How to achieve this and is it even possible?

`CREATE TABLE test2 ( id INTEGER UNSIGNED NOT NULL, subId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, text VARCHAR(45) NOT NULL, UNIQUE INDEX abc (id, subId) ) ENGINE = InnoDB;` -- Incorrect table definition; there can only be one auto column and it must be defined as a key. — jurchiks, May 26 '11 at 14:37

score 2 · Accepted Answer · answered May 08 '14 at 15:50

I had to deal with a similar problem ordering a category tree unnaturally. If you are inserting all the rows for one id at one time, you could do something like this for each subId:

SET @seq = 0;
INSERT INTO test
  (id,  subId,            text) VALUES
  (_id, @seq := @seq + 1, 'Some text')
;

If you need to "add" a row to an id, you can set @seq with

SELECT IFNULL(MAX(subId), 0) INTO @seq FROM test WHERE id = _id;

This would of course require management of id by the application and not mySQL.

You could do that same as the last code block to get the next available id as well.

score 2 · Answer 2 · answered May 26 '11 at 13:09

It may not be exactly what you want and it only works with MyISAM and BDB tables, but I think the closest you will come to what you're asking for is a grouped primary key.

From the manual:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
Which returns: 
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

I'm using InnoDB and enum for ID is not what I'm looking for so I guess it won't fly for me. But maybe someone else will find it useful :) — jurchiks, May 26 '11 at 14:34

MySQL auto-increment on a non-primary key

2 Answers2

Linked