MySQL error 1022 when creating table

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!

The problem is that the name of a foreign key can not be the same as another foreign key in the entire model.

Imagine this situation

Catalog --> Supplier

Product --> Supplier

if the name of the foreign key in table Catalog for supplier is "supplier" and you assigned the same name in product table then the foreign keys names will "collide".

You need to name them differently..

For example:

catalog_supplier product_supplier

It seems you're creating an index on the foreign key columns. When creating a foreign key in InnoDb, one will be created automatically.

See this thread.

MySQL error 1022 when creating table

Related

Recent Posts