Re: Error 1022: Can't write; duplicate key

[Pablo Criscuolo]

MySQL Workbench came up with the following SQL to create a table:

CREATE TABLE IF NOT EXISTS `mydb`.`errors_reports` (
`error_id` INT NOT NULL ,
`report_short` VARCHAR(15) NOT NULL ,
PRIMARY KEY (`error_id`, `report_short`) ,
INDEX `error_id_idx` (`error_id` ASC) ,
INDEX `report_short_idx` (`report_short` ASC) ,
CONSTRAINT `error_id`
FOREIGN KEY (`error_id` )
REFERENCES `mydb`.`errors` (`error_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `report_short`
FOREIGN KEY (`report_short` )
REFERENCES `mydb`.`reports` (`report_short` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

which looks fine to me, and there are a bunch of other very similar tables in my database which MySQL was perfectly happy to create.

But this one...

ERROR 1022 (23000): Can't write; duplicate key in table 'errors_reports'

I can't for the life of me see any duplicate keys here. There's only one key defined!

I'm running MySQL 5.6 with a fresh default install. There's nothing in the error log.

Ideas?

Edit: through a process of elimination (going back to the simplest possible definition of the table, then gradually adding bits back in) the problem appears to be this bit:

CONSTRAINT `error_id`
FOREIGN KEY (`error_id` )
REFERENCES `mydb`.`errors` (`error_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,

which is particularly odd as there is identical code in several other table definitions and those are perfectly okay!