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:

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 :)

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