创建表时 MySQL 错误 1022
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15014592/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL error 1022 when creating table
提问by Matt McLeod
MySQL Workbench came up with the following SQL to create a table:
MySQL Workbench 提出了以下 SQL 来创建表：
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.
这对我来说看起来不错，而且我的数据库中有一堆其他非常相似的表，MySQL 非常乐意创建这些表。
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.
我正在使用全新的默认安装运行 MySQL 5.6。错误日志中没有任何内容。
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!
这特别奇怪，因为在其他几个表定义中有相同的代码，而且这些代码完全没问题！
回答by cSn
The problem is that the nameof a foreign keycan not be the sameas another foreign keyin 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".
如果表 Catalog 中供应商的外键名称为“供应商”，并且您在产品表中分配了相同的名称，则外键名称将“冲突”。
You need to name them differently..
您需要以不同的方式命名它们..
For example:
例如：
catalog_supplier product_supplier
catalog_supplier product_supplier
回答by Kermit
It seems you're creating an index on the foreign key columns. When creating a foreign key in InnoDb, one will be created automatically.
您似乎正在外键列上创建索引。在 InnoDb 中创建外键时，会自动创建一个。
回答by jacek_podwysocki
Try using INSERT IGNOREinstead of INSERTwhere INSERT IGNORE will not insert a new row if a duplicate primary key is found. This should help resolve the problem temporary but I would recommend truncating the table.
如果找到重复的主键，请尝试使用INSERT IGNORE而不是INSERTwhere INSERT IGNORE 不会插入新行。这应该有助于暂时解决问题，但我建议截断表格。
