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

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