无法插入重复键 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15069177/
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
Cannot insert duplicate key SQL
提问by enigma
insert into A (id,Name)
select ti.id,ti .Name
from A ti
where ti.id >= 1 AND ti.id<=3
idis the primary key but not autogenerated. When I run the query I get an error
id是主键但不是自动生成的。当我运行查询时出现错误
Violation of PRIMARY KEY constraint 'XPKA'. Cannot insert duplicate key in object 'dbo.A'
违反 PRIMARY KEY 约束“XPKA”。无法在对象“dbo.A”中插入重复键
tabel A
表A
id Name
1 A
2 B
3 C
and I want to insert
我想插入
id Name
4 A
5 B
6 C
回答by Yaakov Ellis
Every row must have a different value for the Primary Key column. You are inserting the records from Aback into itself, thus you are attempting to create a new row using a Primary Key value that is already being used. This leads to the error message that you see.
对于主键列，每一行都必须具有不同的值。您正在将记录从A后面插入到自身中，因此您正在尝试使用已在使用的主键值创建一个新行。这会导致您看到的错误消息。
If you must insert records in this fashion, then you need to include a strategy for including unique values in the PK Column. If you cannot use an autoincrementrule (the normal method), then your logic needs to enforce this requirement, otherwise you will continue to see errors like this.
如果您必须以这种方式插入记录，那么您需要包含一个策略，用于在 PK 列中包含唯一值。如果您不能使用autoincrement规则（正常方法），那么您的逻辑需要强制执行此要求，否则您将继续看到这样的错误。
回答by Jon Egerton
You are selecting from table Aand inserting straight back in to it. This means that the ID values you insert will certainly already be there.
您正在从表中选择A并直接插入其中。这意味着您插入的 ID 值肯定已经存在。
The message says that ID col has a PrimaryKey on it and requires the values in the column to be unique. It won't let you perform the action for this reason.
该消息表示 ID col 上有一个 PrimaryKey，并且要求列中的值是唯一的。由于这个原因，它不会让您执行操作。
To fix your query based on your stated requirement, change the script to:
要根据您声明的要求修复您的查询，请将脚本更改为：
insert into A (id,Name)
select ti.id + 3,ti .Name
from A ti
where ti.id >= 1 AND ti.id<=3
回答by Dan Metheus
You need to adjust the ID of the rows you are inserting. In your example to produce keys 4, 5, 6:
您需要调整要插入的行的 ID。在您生成密钥 4、5、6 的示例中：
insert into A (id,Name)
select ti.id + 3 as NewKey,ti.Name
from A ti
where ti.id >= 1 AND ti.id<=3
But in reality you need to pick a value that will keep your new keys separate from any possible old key, maybe:
但实际上，您需要选择一个值，使您的新密钥与任何可能的旧密钥分开，也许：
insert into A (id,Name)
select ti.id + 100000 as NewKey,ti.Name
from A ti
where ti.id >= 1 AND ti.id<=3
回答by Zeddy
As Yaakov Ellis has said...
正如雅科夫·埃利斯所说......
Every row must have a different value for the Primary Key column.
对于主键列，每一行都必须具有不同的值。
And as you have a WHERE clause which constricts your rows to 3 in total EVER
并且因为您有一个 WHERE 子句，它将您的行总共限制为 3
Those with the unique Id's 1, 2 and 3
具有唯一 ID 1、2 和 3 的那些
So if you want to replace those rather then tring to INSERT them where they already exist and generating your error.
因此，如果您想替换它们而不是尝试将它们插入到它们已经存在的位置并产生错误。
Maybe you could UPDATE them instead? And that will resolve your issue.
也许你可以更新它们？这将解决您的问题。
UPDATE
更新
After your addition of extra code... You should set your UNIQUE Key Identifier to the ID Number and not the ABC field name (whatever you have called it)
添加额外代码后...您应该将您的唯一键标识符设置为 ID 号而不是 ABC 字段名称（无论您怎么称呼它）
