mysql Can't write - duplicate key

Question

I don't get it. Can someone explain my failure here:

CREATE TABLE IF NOT EXISTS `mytable`.`Employee_Service` (
  `Employee_Service_Id` INT NOT NULL,
  `Created_At` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `Updated_At` TIMESTAMP NULL,
  PRIMARY KEY (`Employee_Service_Id`),
  CONSTRAINT `Employee`
    FOREIGN KEY (`Employee_Service_Id`)
    REFERENCES `mytable`.`Employees` (`Employee_Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Service`
    FOREIGN KEY (`Employee_Service_Id`)
    REFERENCES `mytable`.`Services` (`Service_Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

The log-file says:

Error 1022: Can't write; duplicate key in table 'employee_service'

To understand what I want to do with that: The table "Employee_Service" is the connection between the tables "Employees" and "Services". In that table I want to store the "Employee_Id" from the table "Employees" and connect it with the "Service_Id" from the table "Services". So I can get the relation that an employee has a service. But what is wrong with my table, especially with the foreing keys???

Check your other tables to see if you've used the same constraint name `Employee` or `Service`. — Barmar, May 22 '14 at 21:05

score 1 · Accepted Answer · answered May 22 '14 at 21:05

1

Most probably your other table has same constraint named Employee and Service. Another problem with your posted CREATE statement, you are trying to make Employee_Service_Id as both PK and FK

PRIMARY KEY (`Employee_Service_Id`),
  CONSTRAINT `Employee`
    FOREIGN KEY (`Employee_Service_Id`)

answered May 22 '14 at 21:05

Rahul

76,197
13
71
125

Ok I will check this. But the name of the statements is done by MySQL Workbench. Or can I change that for myself? – Timo.Klement May 22 '14 at 21:10
@Tipo, yes you can. do try and if successful accept the answer. – Rahul May 22 '14 at 21:12
Great! There was only one other table that used the same foreign key name. That's really interesting. I'm creating since 11 years many databases and had never such a problem with that. Funny! – Timo.Klement May 22 '14 at 21:21

mysql Can't write - duplicate key

1 Answers1