Violation of PRIMARY KEY constraint with identity column - by mikegoodtampa

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
0
Sign in
to vote
ID 778610 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 2/5/2013 2:00:38 PM
Access Restriction Public

Description

We have table with an identity integer primary key, and a single procedure inserts to it.  This has worked for years and has inserted 100's of millions of rows without problem.  A few months ago we upgraded to SQL 2012, but this code and this table did not change.  Today we were alerted to insert failure, PK violation, error specified the key that was problem. Such a row already existed.  This has only happened once.

We manually verified that the current identity seed is correct and continues to increase as expected. We've run DBCC CHECKIDENT (table, NO_RESEED), came back clean.

The only possibly theory I can come up with is that there is a bug that allows this to happen very rarely--two simultaneous connections get same identity value, one fails.  
Sign in to post a comment.
Posted by ango0001 on 10/24/2016 at 11:36 PM
We have encountered this issue at least 3 times now in the past few months. We have SQL Server Standard Version 11.0.5058. Insert fails with PK violation error on the identity column.
Posted by Heinzi.at on 3/7/2016 at 2:38 AM
Sam here with SQL Server 2012 (11.0.2100.60 x64). Every few months, a single INSERT-VALUES on a table with an IDENTITY field fails with a PK violation. The PK/identity field is not referenced in the INSERT, and we do not mess around with the identity seeds. Details can be found here: <http://dba.stackexchange.com/q/122254/5273>.
Posted by Erik A. Brandstadmoen on 2/22/2016 at 1:19 PM
Example after running DBCC CHECKIDENT:

Checking identity information: current identity value '2135076432', current column value '2135077281'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Posted by Erik A. Brandstadmoen on 2/22/2016 at 1:18 PM
Same problem here - SQL Server 2014 express. Insert into table with Identity column in a transaction, multiple at once (integration test suite). Fails very often. DBCC CHECKIDENT ('table name') solved the problem temporarily. And then, it's back.
Posted by penter92 on 11/30/2015 at 2:15 AM
I have the same issue on sql server 2014, the error raise is the next one:

Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_DIM_EMPLOYEE'. Cannot insert duplicate key in object 'dbo.DIM_EMPLOYEE'. The duplicate key value is (2001).".

This is an identity field, and the pk is referring only to this field.
Posted by Rb_Brewer on 1/7/2014 at 11:59 AM
I am having similar issue but with Sql Server 2008 R2 SP2. I can insert thousands of records to my table and then suddenly get a duplicate primary key error on an identity column. I never specify the column and the seed looks good. Please advise.
Posted by Bryan [MSFT] on 3/14/2013 at 5:38 PM
Dear Customer,

I am the dev owning the identity feature. Thanks for reporting the bug.

The identity feature has been changed in SQL2012, so I need to investigate your case with full details.
Can you try to give me a report or the dump when the error is raised?
Also, I may need the sp that you descibed from your report.
If you can give me the above, then I may be able to find the root cause.

Thanks very much!
Bryan