创建表时 MySQL 错误 1022 [英] MySQL error 1022 when creating table

问题描述

MySQL Workbench 想出了以下 SQL 来创建表:

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

这对我来说看起来不错，而且我的数据库中还有一堆其他非常相似的表，MySQL 非常乐意创建这些表.

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.

但是这个...

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!

我正在使用全新的默认安装运行 MySQL 5.6.错误日志中没有任何内容.

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

想法?

通过消除过程(回到表的最简单的可能定义，然后逐渐添加位)问题似乎是这一点:

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.

想象一下这种情况

目录 --> 供应商

产品 --> 供应商

如果表 Catalog 中供应商的外键名称为供应商"，而您在产品表中分配了相同的名称，那么外键名称将冲突".

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..

例如:

catalog_supplier产品供应商

catalog_supplier product_supplier

这篇关于创建表时 MySQL 错误 1022的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！