SQL Server create primary key constraint duplicate key error

I have been experiencing some strange behaviour with one of my SQL commands taken from one of our stored procedures.

This command follows the below order of execution:

1) Drop table

2) Select * into table name from live server

3) Alter table to apply PK - this step fails once out of 4 daily executions

My SQL statement:

 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' 
 [inf].[tblBase_MyTable]') AND type in (N'U'))
 DROP TABLE [inf].[tblBase_MyTable]

 SELECT * INTO [inf].[tblBase_MyTable]
 FROM LiveServer.KMS_ALLOCATION WITH (NOLOCK)

 ALTER TABLE [inf].[tblBase_MyTable] ADD  
 CONSTRAINT [PK_KMS_ALLOCATION] PRIMARY KEY NONCLUSTERED 
 (
   [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = 
 OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

 GRANT SELECT ON [inf].[tblBase_MyTable] TO ourGroup

This is very strange considering the table is dropped, and I thought the indexes / keys would also be dropped. However I get this error at the same time every day. Any advice would be very much appreciated.

Error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'inf.tblBase_MyTable' and the index name 'PK_KMS_ALLOCATION'.

Duplicate keys in [inf].[tblBase_MyTable] table are actually possible thanks to the WITH (NOLOCK) hint which allows "dirty reads". Have a look at blog which describes this in detail: SQL Server NOLOCK Hint & other poor ideas:

What many people think NOLOCK is doing

Most people think the NOLOCK hint just reads rows & doesn’t have to wait till others have committed their updates or selects. If someone is updating, that is OK. If they’ve changed a value then 99.999% of the time they will commit, so it’s OK to read it before they commit. If they haven’t changed the record yet then it saves me waiting, its like my transaction happened before theirs did.

The Problem

The issue is that transactions do more than just update the row. Often they require an index to be updated OR they run out of space on the data page. This may require new pages to be allocated & existing rows on that page to be moved, called a PageSplit. It is possible for your select to completely miss a number of rows &/or count other rows twice.

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in , dbUpdate(sql);. It is working correctly, but it is also outputting the following SQL error: Violation of PRIMARY KEY constraint 'PK_AC_Shipping_Addresses'. Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Order_Details'. Cannot insert duplicate key in object 'dbo.Order Details'. The duplicate key value is (10248, 11). The statement has been terminated.

Well... you might have to repeat creating the new table and filling it until the check-query from @DarkoMartinovic does not return duplicates. Only then you can continue to add the PK. But this solution might cause heavy load on your live system. And you nave no guarantee that you have a 1:1 copy of the data as well.

Violation of constraint ''. Cannot insert duplicate key in object , SQL Server's error “Violation of … constraint '…'. user tries to insert a non- unique value into a table's unique column such as the primary key. SQL Server Primary Key Constraints and Violations – Querychat The primary key constraint is one of database constraints that is used to enforce the uniqueness of every row in a table. When we insert a row into a table, we might accidentally insert duplicate values and cause an error.

Having reviewed various helpful comments here, I have decided against (for now) implementing SNAPSHOT isolation as this interface does not make use of a proper staging environment.

To move to this would mean either creating a staging area and setting that database to READ COMMITTED SNAPSHOT isolation, and a rebuild of the entire interface.

To that end and on the basis of saving development time, we have opted for ensuring that any ghost reads where dupes could be brought across from the source are handled before applying the PK.

This is by no means an ideal solution in terms of performance on the target server but will provide some headroom for now and certainly remove the previous error.

SQL approach below:

DECLARE @ALLOCTABLE TABLE 
(SEQ INT, ID NVARCHAR(1000), CLASSID NVARCHAR(1000), [VERSION] NVARCHAR(25), [TYPE] 
NVARCHAR(100), VERSIONSEQUENCE NVARCHAR(100), VERSIONSEQUENCE_TO NVARCHAR(100), 
BRANCHID NVARCHAR(100), ISDELETED INT, RESOURCE_CLASS NVARCHAR(25), RESOURCE_ID 
NVARCHAR(100), WARD_ID NVARCHAR(100), ISCOMPLETE INT, TASK_ID NVARCHAR(100));

------- ALLOCATION
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[inf]. 
[tblBase_MyTable]') AND type in (N'U'))
DROP TABLE [inf].[tblBase_MyTable]

SELECT * INTO [inf].[tblBase_MyTable]
FROM LiveServer.KMS_ALLOCATION WITH (NOLOCK)

INSERT INTO @ALLOCTABLE
SELECT *
  FROM
   (SELECT 
   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ISCOMPLETE DESC) SEQ, AL.*
   FROM [inf].[tblBase_MyTable] AL
 )DUPS
 WHERE SEQ >1

 DELETE FROM [inf].[tblBase_MyTable]
 WHERE ID IN (SELECT ID FROM @ALLOCTABLE)
 AND ISCOMPLETE = 0

ALTER TABLE [inf].[tblBase_MyTable] ADD  CONSTRAINT 
[PK_KMS_ALLOCATION] PRIMARY KEY NONCLUSTERED 
  (
   [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GRANT SELECT ON [inf].[tblBase_MyTable] TO OurGroup

How to solve violation of PRIMARY KEY constraint 'PK_teacher , You are trying to insert a value into Teacher table primary key field that already exists. As the error says, you're trying to insert a record to the table with a key value when inserting the data or alter the key to contain desired columns. If the key is an identity column it shouldn't cause duplicates unless� Your data (target table) won't have duplicates obviously because there's a primary key constraint. . The problem is that you're trying to insert a record (source data) which the primary key already exists in the target.

As you are pretty sure that there are no duplicates, Can you please check another issue before creating PK that is the column ID is allowing NULL or not. If NULL allowed, try to create PK after setting the column ID to NOT NULL. SQL Server do not allow to create PK on a column that allow NULL in it.

SQL Server FAQ, What Happens If You Insert a Duplicate Key for the Primary Key Column? SQL Server FAQ - Duplicate Key Error on Primary Key Columns NULL, 0, '2006-04- 30') GO Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__fyi_links__03317E3D'. How To Create a Testing Table with Test Data? This has happened because the INSERT or UPDATE statement has created a duplicate value in a field that has either a PRIMARY KEY constraint or a UNIQUE constraint. System Status. tag like :tagString"). "[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint "XXXXXXXX".

Violation of PRIMARY KEY constraint, Cannot insert duplicate key in object Table Name. SQL Server Error Messages - Msg 2627 - Violation of PRIMARY KEY constraint Constraint Name. Cannot� Use IGNORE_DUP_KEY = OFF during primary key definition to ignore the duplicates while insert. for example . create table X( col1..) CONSTRAINT [pk_X] PRIMARY KEY CLUSTERED ( )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY] ) ON [PRIMARY]

Why am I getting a primary/unique key violation?, This is by far the most common cause of a unique/primary key error that I see. Cannot insert duplicate key row in object 'Person. T-SQL | Tags: code language, language sql, microsoft sql server, problem resolution, sql� It is used alongside relational operators to check whether a value satisfies the condition or not (boolean). If the condition is satisfied, the boolean expression sets to True otherwise False. The check constraint does not have a specific syntax. It is used along with the create table syntax. An

** Troubleshooting ** "Violation of PRIMARY KEY constraint , Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL erver]Violation of PRIMARY KEY constraint 'PKNC_XDB10'. Cannot insert duplicate� SQL PRIMARY KEY Constraint. The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

Comments
  • It seems duplicate records are there in the column you are creating PK for. As PK column do not allow duplicates, you first need to remove duplicates from the table and then create the PK.
  • but the table is dropped?
  • there are no duplicates on this key within the live system
  • In your step-2, you are inserting records first before creating the PK.
  • @Diado yes that where I am at with it, It's looking like data changes undelying the READ COMMITTED model is the issue. I will look to mograte to a SNAPSHOT
  • Although very useful information I am accutely aware of the trade off here
  • How big is the source table? How frequently data is changed? Have you considered CDC?
  • I agree this would place heavy load onto the system, would it be bettr therefore to move to a READ COMMITTED SNAPSHOT ISOLATION model?
  • You might certainly try that. @Diado also pointed it out as a possible solution. If it works, you have guarantee that you have a valid copy of the data. However, I have little experience with various isolation levels, so I cannot ad-hoc provide any do's and don'ts in that respect.
  • The ID values are never NULL, since they come from a table that already has a primary key on those values. So this should not be the issue.