SQL Server 违反 PRIMARY KEY 约束 'PK
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8090671/
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
SQL Server Violation of PRIMARY KEY constraint 'PK
提问by creativegenius
I am getting this error when I am checking the logs of my application:
我在检查应用程序日志时收到此错误：
[Databases] Exception : dbi.integrity-error, [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__tblRaw201131411__13D55980'. Cannot insert duplicate key in object 'dbo.tblRaw201131411'. in EXEC
[Databases] Exception : dbi.integrity-error, [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__tblRaw201131412__407DE2C5'. Cannot insert duplicate key in object 'dbo.tblRaw201131412'. in EXEC
File "mssqlinterface.py", line 951, in executeSQLStatement
[数据库] 异常：dbi.integrity-error，[Microsoft][ODBC SQL Server 驱动程序][SQL Server]违反 PRIMARY KEY 约束“PK__tblRaw201131411__13D55980”。无法在对象“dbo.tblRaw201131411”中插入重复键。在执行
[数据库] 异常：dbi.integrity-error，[Microsoft][ODBC SQL Server 驱动程序][SQL Server]违反 PRIMARY KEY 约束“PK__tblRaw201131412__407DE2C5”。无法在对象“dbo.tblRaw201131412”中插入重复键。在执行
文件“mssqlinterface.py”，第 951 行，在 executeSQLStatement 中
How to fix this error?
如何修复此错误？
My application is using a SQL Server 2005Database.
我的应用程序正在使用SQL Server 2005数据库。
回答by paxdiablo
You can fix this error by not trying to insert rows with a duplicate primary key.
您可以通过不尝试插入具有重复主键的行来修复此错误。
Howyou do that depends a great deal on your application, the details of which you haven't provided (lines 900 through 960 of mssqlinterface.pywould be a good start with a special marker on line 951).
您如何做到这一点在很大程度上取决于您的应用程序，您尚未提供详细信息（第 900 行到 960mssqlinterface.py行将是一个良好的开端，在第 951 行使用特殊标记）。
Perhaps you're trying to insert when you should update. Perhaps the code that sets the primary key value is deficient in some manner.
也许您正试图在应该更新时插入。也许设置主键值的代码在某些方面存在缺陷。
The possibilities are literally (and I mean that in a very figurative sense), endless.
可能性是字面上的（我的意思是在非常比喻意义上），无穷无尽。
回答by nathan gonzalez
don't insert the same value into a primary key column twice...?
不要将相同的值插入主键列两次......？
回答by Purplegoldfish
Since this is a Primary key violation you are trying to insert something which will make the data used by the primary key not unique.
由于这是主键违规，您试图插入一些会使主键使用的数据不唯一的内容。
1) You can simply not do this, make sure you dont add duplicate PK data
1）你可以不这样做，确保你不添加重复的PK数据
2) Maybe you need to update your primary key to be a compound key (Key uses 2 or more columns) so the data used by the PK can be unique.
2）也许您需要将主键更新为复合键（Key 使用 2 列或更多列），以便 PK 使用的数据可以是唯一的。
3) If you are manually entering the data you are using as your PK maybe add another column named ID to this table (if appropriate) and set it to automatically increment itself then make this your PK, thus your PK will always be unique.
3) 如果您手动输入您用作 PK 的数据，则可以向该表中添加另一列名为 ID 的列（如果适用）并将其设置为自动递增，然后将此设为您的 PK，因此您的 PK 将始终是唯一的。
4) Remove the PK (this is generally a really bad idea)
4）删除PK（这通常是一个非常糟糕的主意）
Without more data it really is hard to offer a solution but at a glance these 4 options are what I would suggest.
没有更多数据，确实很难提供解决方案，但乍一看，这 4 个选项是我的建议。
回答by live-love
Had the same error. Identity column doesn't guarantee uniqueness of ids. Here's some code to reproduce the error, and a possible solution:
有同样的错误。Identity 列不保证 id 的唯一性。这是一些重现错误的代码，以及可能的解决方案：
CREATE TABLE atest (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY , val VARCHAR(10));
insert atest values ('a')
insert atest values ('b')
insert atest values ('c')
insert atest values ('d')
insert atest values ('e')
delete atest where id < 4
select * from atest
--4 d
--5 e
DBCC CHECKIDENT ('atest', RESEED, 0);
insert atest values ('aa')
insert atest values ('bb')
insert atest values ('cc')
select * from atest
--1 aa
--2 bb
--3 cc
--4 d
--5 e
-- Here comes the error!
insert atest values ('dd')
-- Oops!
-- Here's the Solution:
DBCC CHECKIDENT ('atest', RESEED);
-- Try again...
insert atest values ('dd')
select * from atest
--1 aa
--2 bb
--3 cc
--4 d
--5 e
--6 dd
--All good!
回答by HLGEM
It looks as if you are trying to import a file that has duplicate records. Generally I import those to a staging table and do a data clean up to get rid of them before importing to a production table. You could also remove the duplicate records through an SSIS pacakage dataflow. Most important is to find out why the file has duplicate records and what that means in terms of the data model you are trying to import to. For instance suppose you were importing a roster fo sales reps who each have a territory. Assume your table considers the terrtory to be unique and thus made it the PK. Then you get a new client and you get a PK error because they have two reps who share a territory. Just deleting the records won't do you any good (those reps would not be able to access your system), you now need to fix your application to handle reps who share terrtories. In another case, it might be that they forgot to remove a record of a person who is no longer there and you can ask for a fixed file and go on happily with your current process.
看起来好像您正在尝试导入具有重复记录的文件。通常我将它们导入到临时表并在导入到生产表之前进行数据清理以摆脱它们。您还可以通过 SSIS 包数据流删除重复记录。最重要的是找出文件有重复记录的原因，以及这对您尝试导入的数据模型意味着什么。例如，假设您要为每个都有一个区域的销售代表导入名册。假设您的表认为领土是唯一的，因此将其设为 PK。然后你得到一个新客户，你得到一个 PK 错误，因为他们有两个共享一个区域的代表。只是删除记录对您没有任何好处（那些代表将无法访问您的系统），您现在需要修复您的应用程序以处理共享领土的代表。在另一种情况下，可能是他们忘记删除不再存在的人的记录，您可以要求一个固定的文件并愉快地继续您当前的流程。
