mysql 1022错误,创建表时出现MySQL错误1022

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