Creating second PK as AI in MySQL

Question

I am trying to create a simple database with two tables:

Group
Participants

as in the attached screen shot:

enter image description here

Well what I would like to has is the id of a participat to be an auto increment as a second PK for the table, so I would have entries like

|ID    | group_id |
|  1   | 1        |
|  2   | 1        |
|  3   | 1        |
|  1   | 2        |
|  2   | 2        |
|  3   | 2        |

and so on...

I've followed the answer here: https://stackoverflow.com/a/5416667/1358670 and following is MySQL.

CREATE TABLE IF NOT EXISTS `test`.`participant` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `goup_id` INT(11) NOT NULL,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`goup_id`, `id`),
  INDEX `fk_participant_goup_idx` (`goup_id` ASC),
  CONSTRAINT `fk_participant_goup`
    FOREIGN KEY (`goup_id`)
    REFERENCES `test`.`goup` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

But I keep getting the following error:

ERROR: Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

Any help would be appreciated :)

score 1 · Accepted Answer · edited Nov 28 '13 at 17:17

1

You cannot have an auto_increment field on an InnoDB table if it is not the first field of the PK

Try replacing

PRIMARY KEY (`goup_id`, `id`)

with

PRIMARY KEY (`id`)

or if you like

PRIMARY KEY (`id`, `goup_id`)

in fact for InnoDB the auto_increment column must be the first of the PK.

edited Nov 28 '13 at 17:17

Abu Romaïssae

3,841
5
37
59

answered Nov 20 '13 at 17:35

Matteo Tassinari

18,121
8
60
81

hum... the second or the third one does not suite my need – Abu Romaïssae Nov 20 '13 at 17:59
Still, you cannot have an auto_increment field on an InnoDB table if it is not the first field of the PK... That said, do as you like. – Matteo Tassinari Nov 20 '13 at 20:42
OKay, thanks for your time, that means I should think about some work around :) – Abu Romaïssae Nov 20 '13 at 21:04
You could switch to MyISAM and drop the FK, which would be useless then. – Matteo Tassinari Nov 20 '13 at 21:29
Yeah, but still I'm not allowed to switch to MyISAM :( – Abu Romaïssae Nov 20 '13 at 21:48
Why can't you use a simple auto increment key as in the group table? – Matteo Tassinari Nov 23 '13 at 07:32
This is not my real database, in my real project I need to keep a files tack per user, and I must let the incrementation goes sequencially per user – Abu Romaïssae Nov 23 '13 at 09:55

Creating second PK as AI in MySQL

1 Answers1