#1022 - Can't write; duplicate key in table

Question

Hi i am I'm getting a 1022 error on create tables in mysql phpMyAdmin. the error is on the last table "usercoment":

SOLUTION EDIT:Ok,I had already a Constraint key named IdMovie,so i renamed and it works fine . Thank you guys.

Here are the tables:

CREATE TABLE IF NOT EXISTS `WhichMovie`.`UserComent` (
  `IDComent` INT NOT NULL,
  `IDUser` INT NOT NULL,
  `IDMovie` INT NOT NULL,
  `date` TIMESTAMP NOT NULL,
  PRIMARY KEY (`IDComent`, `IDUser`, `IDMovie`, `date`),
  INDEX `idUser_idx` (`IDUser` ASC),
  INDEX `idMovie_idx` (`IDMovie` ASC),
  CONSTRAINT `IdComent`
    FOREIGN KEY (`IDComent`)
    REFERENCES `WhichMovie`.`Coment` (`idComent`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `IiUser`
    FOREIGN KEY (`IDUser`)
    REFERENCES `WhichMovie`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `IdMovie`
    FOREIGN KEY (`IDMovie`)
    REFERENCES `WhichMovie`.`Movie` (`idMovie`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `WhichMovie`.`Movie`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `WhichMovie`.`Movie` (
  `idMovie` INT NOT NULL AUTO_INCREMENT,
  `Ttle` VARCHAR(45) NULL,
  `Year` INT NULL,
  `Country` VARCHAR(45) NULL,
  `Poster` BLOB NULL,
  `Rating` DECIMAL NULL,
  PRIMARY KEY (`idMovie`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `WhichMovie`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `WhichMovie`.`User` (
  `idUser` INT NOT NULL AUTO_INCREMENT,
  `Username` VARCHAR(45) NOT NULL,
  `Email` VARCHAR(45) NOT NULL,
  `Country` VARCHAR(45) NULL,
  `Pass` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idUser`, `Username`, `Email`),
  UNIQUE INDEX `idUser_UNIQUE` (`idUser` ASC),
  UNIQUE INDEX `Email_UNIQUE` (`Email` ASC),
  UNIQUE INDEX `Username_UNIQUE` (`Username` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `WhichMovie`.`Coment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `WhichMovie`.`Coment` (
  `idComent` INT NOT NULL AUTO_INCREMENT,
  `NameComent` VARCHAR(45) NULL,
  PRIMARY KEY (`idComent`),
  UNIQUE INDEX `idComent_UNIQUE` (`idComent` ASC))
ENGINE = InnoDB;

Can anyone see it ?

You should also give us other tables structure that are referenced with UserComent — Marcin Nabiałek, May 13 '14 at 15:45
It seems the problem is not with structure. I have no problem with creating tables and inserting data. Are you 100% sure the problem is not when you insert data? Rename your whichmovie database, create empty whichmovie database and just import those queries as in my answer. Do you really see error when you launch those queries while creating tables? — Marcin Nabiałek, May 13 '14 at 16:30

score 7 · Answer 1 · edited May 23 '17 at 12:13

7

According to: MySQL error 1022 when creating table You can't have a foreign key constraint name duplicated over the entire model.

edited May 23 '17 at 12:13

Community

1
1

answered May 13 '14 at 15:47

Tripp Kinetics

5,178
2
23
37

score 4 · Answer 2 · answered May 13 '14 at 15:59

4

Just rename The constraints names differently and your problem is solved...

Example :

 CONSTRAINT `IdComent_CONSTRAINT`
    FOREIGN KEY (`IDComent`)

Now you have a CONSTRAINT'NAME different from THE FOREIGN KEY ONE

answered May 13 '14 at 15:59

ASNAOUI Ayoub

462
2
9

Marcin Nabiałek · Answer 3 · 2014-05-13T16:20:29.367

What version of MySQL are you using?

On my localhost with MySQL 5.6.12 this code (same as yours but creation of UserComent moved at the end) works fine:

-- -----------------------------------------------------
-- Table `WhichMovie`.`Movie`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `WhichMovie`.`Movie` (
  `idMovie` INT NOT NULL AUTO_INCREMENT,
  `Ttle` VARCHAR(45) NULL,
  `Year` INT NULL,
  `Country` VARCHAR(45) NULL,
  `Poster` BLOB NULL,
  `Rating` DECIMAL NULL,
  PRIMARY KEY (`idMovie`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `WhichMovie`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `WhichMovie`.`User` (
  `idUser` INT NOT NULL AUTO_INCREMENT,
  `Username` VARCHAR(45) NOT NULL,
  `Email` VARCHAR(45) NOT NULL,
  `Country` VARCHAR(45) NULL,
  `Pass` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idUser`, `Username`, `Email`),
  UNIQUE INDEX `idUser_UNIQUE` (`idUser` ASC),
  UNIQUE INDEX `Email_UNIQUE` (`Email` ASC),
  UNIQUE INDEX `Username_UNIQUE` (`Username` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `WhichMovie`.`Coment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `WhichMovie`.`Coment` (
  `idComent` INT NOT NULL AUTO_INCREMENT,
  `NameComent` VARCHAR(45) NULL,
  PRIMARY KEY (`idComent`),
  UNIQUE INDEX `idComent_UNIQUE` (`idComent` ASC))
ENGINE = InnoDB;




CREATE TABLE IF NOT EXISTS `WhichMovie`.`UserComent` (
  `IDComent` INT NOT NULL,
  `IDUser` INT NOT NULL,
  `IDMovie` INT NOT NULL,
  `date` TIMESTAMP NOT NULL,
  PRIMARY KEY (`IDComent`, `IDUser`, `IDMovie`, `date`),
  INDEX `idUser_idx` (`IDUser` ASC),
  INDEX `idMovie_idx` (`IDMovie` ASC),
  CONSTRAINT `IdComent`
    FOREIGN KEY (`IDComent`)
    REFERENCES `WhichMovie`.`Coment` (`idComent`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `IiUser`
    FOREIGN KEY (`IDUser`)
    REFERENCES `WhichMovie`.`User` (`idUser`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `IdMovie`
    FOREIGN KEY (`IDMovie`)
    REFERENCES `WhichMovie`.`Movie` (`idMovie`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Tables are created, no error occurs.

On 5.6.15 tables are also created without a problem. Below the view from phpmyadmin designer:

#1022 - Can't write; duplicate key in table

3 Answers3