MySQL 错误 1022 - 无法写入；表中的重复键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18056786/
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
Error 1022 - Can't write; duplicate key in table
提问by Git-able
I'm getting a 1022 error regarding duplicate keys on create table command. Having looked at the query, I can't understand where the duplication is taking place. Can anyone else see it?
我在 create table 命令上收到关于重复键的 1022 错误。查看了查询后，我无法理解重复发生的位置。其他人能看到吗？
SQL query:
-- -----------------------------------------------------
-- Table `apptwo`.`usercircle`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `apptwo`.`usercircle` (
`idUserCircle` MEDIUMINT NOT NULL ,
`userId` MEDIUMINT NULL ,
`circleId` MEDIUMINT NULL ,
`authUser` BINARY NULL ,
`authOwner` BINARY NULL ,
`startDate` DATETIME NULL ,
`endDate` DATETIME NULL ,
PRIMARY KEY ( `idUserCircle` ) ,
INDEX `iduser_idx` ( `userId` ASC ) ,
INDEX `idcategory_idx` ( `circleId` ASC ) ,
CONSTRAINT `iduser` FOREIGN KEY ( `userId` ) REFERENCES `apptwo`.`user` (
`idUser`
) ON DELETE NO ACTION ON UPDATE NO ACTION ,
CONSTRAINT `idcategory` FOREIGN KEY ( `circleId` ) REFERENCES `apptwo`.`circle` (
`idCircle`
) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB;
MySQL said: Documentation
#1022 - Can't write; duplicate key in table 'usercircle'
回答by Maksym Polshcha
The most likely you already have a constraint with the name iduseror idcategoryin your database. Just rename the constraints if so.
很可能您已经在名称iduser或idcategory数据库中设置了约束。如果是这样，只需重命名约束。
Constraints must be unique for the entire database, not just for the specific table you are creating/altering.
约束对于整个数据库必须是唯一的，而不仅仅是对于您正在创建/更改的特定表。
To find out where the constraints are currently in use you can use the following query:
要找出当前使用约束的位置，您可以使用以下查询：
SELECT `TABLE_SCHEMA`, `TABLE_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_NAME` IN ('iduser', 'idcategory');
回答by Wassim Sabra
Change the Foreign key name in MySQL. You can not have the same foreign key names in the database tables.
更改 MySQL 中的外键名称。数据库表中不能有相同的外键名称。
Check all your tables and all your foreign keys and avoid having two foreign keys with the same exact name.
检查所有表和所有外键，避免使用两个完全相同的外键。
回答by Chandz
From the two linksResolved Successfullyand Naming Convention, I easily solved this same problem which I faced. i.e., for the foreign key name, give as fk_colName_TableName. This naming convention is non-ambiguous and also makes every ForeignKey in your DB Model unique and you will never get this error.
从Resolved Successfully和Naming Convention的两个链接中，我轻松解决了我面临的同样问题。即，对于外键名称，给出fk_colName_ TableName。此命名约定是明确的，并且还使您的数据库模型中的每个外键都是唯一的，您永远不会遇到此错误。
Error 1022: Can't write; duplicate key in table
错误 1022：无法写入；表中的重复键
回答by Simon East
As others have mentioned, it's possible that the name for your constraint is already in use by another table in your DB. They must be unique across the database.
正如其他人所提到的，您的约束的名称可能已被您的 DB 中的另一个表使用。它们在整个数据库中必须是唯一的。
A good convention for naming foreign key constraints is:
命名外键约束的一个好的约定是：
fk_TableName_ColumnName
To investigate whether there's a possible clash, you can list all constraints used by your database with this query:
要调查是否存在可能的冲突，您可以使用以下查询列出数据库使用的所有约束：
SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'YOUR_DB';
When I ran this query, I discovered I had previously made a temporary copy of a table and this copy was already using the constraint name I was attempting to use.
当我运行这个查询时，我发现我之前制作了一个表的临时副本，这个副本已经在使用我试图使用的约束名称。
回答by Unfamiliar Script
I just spent the last 4 hours with the same issue. What I did was to simply make sure the constraints had unique names.
我刚刚在过去的 4 个小时里遇到了同样的问题。我所做的只是确保约束具有唯一的名称。
You can rename the constraints. I appended a number to mine so I could easily trace the number of occurrences.
您可以重命名约束。我在我的后面附加了一个数字，这样我就可以轻松地跟踪出现的次数。
Example
例子
If a constraint in a table is named boy with a foreign key X The next constraint with the foreign key X can be called boy1
如果表中的约束名为 boy，外键为 X 下一个外键为 X 的约束可以称为 boy1
I'm sure you'd figure out better names than I did.
我相信你会想出比我更好的名字。
回答by MJD
This can also arise in connection with a bug in certain versions of Percona Toolkit's online-schema-change tool. To mutate a large table, pt-osc first creates a duplicate table and copies all the records into it. Under some circumstances, some versions of pt-osc 2.2.x will try to give the constraints on the new table the same names as the constraints on the old table.
这也可能与某些版本的 Percona Toolkit 的在线模式更改工具中的错误有关。要对大表进行变异，pt-osc 首先创建一个重复表并将所有记录复制到其中。在某些情况下，某些版本的 pt-osc 2.2.x 会尝试为新表上的约束赋予与旧表上的约束相同的名称。
A fix was released in 2.3.0.
2.3.0 中发布了一个修复程序。
See https://bugs.launchpad.net/percona-toolkit/+bug/1498128for more details.
有关更多详细信息，请参阅https://bugs.launchpad.net/percona-toolkit/+bug/1498128。
回答by MJD
I also encountered that problem.Check if database name already exist in Mysql,and rename the old one.
我也遇到了这个问题。检查Mysql中是否已经存在数据库名称，并重命名旧的。
回答by user3076750
I had this problem when creating a new table. It turns out the Foreign Key name I gave was already in use. Renaming the key fixed it.
创建新表时遇到了这个问题。原来我给的外键名称已经被使用了。重命名密钥修复了它。
