How to troubleshoot Error 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

29 May,2015 by Jack Vamvas

Question:   I’m investigating a persistent error message Error 2627

Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

How can I troubleshoot and fix?

Answer:  In summary , the unique index on the column is preventing the statement from  commiting.

Another record exists with the same value on the unique index column.

Error 2627 is closely related  to  a constraint violation. Constraints are similar to Unique Indexes – when a UNIQUE CONSTRAINT is created a UNIQUE INDEX is automatically created. Read more on Difference between UNIQUE CONSTRAINT versus UNIQUE INDEX

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.

 

Data is validated in the same way and both check for unique data

The first step is to identify the sql statements causing the Exception. SQL Server Profiler can display the Exceptions.

Once you’ve identified the sql statements – work is required to understand why the application is attempting to insert  duplicate values into the UNIQUE INDEX.

It is easy to identify the record already in the table. Use the value from the statement throwing the exception. Run a SELECT statement on the table , to return the row already in the table.

Some methods to avoid the error include:

a)      Use the IF NOT EXISTS logic

b)      Use the NOT EXISTS logic

Read More on data integrity and SQL Developers

Where to maintain data integrity rules?

SQL server – 5 things SQL Server developers should know about SQL
Server

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to troubleshoot Error 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer