SQL: Violation of SQL Server UNIQUE KEY constraint but entry doesn't already exist

One issue that comes up time and again in the forums is when an INSERT statement fails with a violation of a UNIQUE or PRIMARY KEY constraint but when the user checks the existing table, the value that’s being complained about isn’t already in the table.

The Symptom

For example, a table might have primary key values of 2, 12, and 14.

When an INSERT is performed, there is an error telling you that you can’t insert a duplicate key value of say 15.

You check the table and there is no row with a primary key value of 15.

The same issue can happen with UPDATE statements.

What Usually Causes This

Whenever you see this, the issue is almost always that the duplicate values exist in the data that you’re trying to insert, and that’s why it can’t insert.

In the example above, it would be because the input rows contain two or more rows where the primary key value is 15.

Often this will be caused by a join that is producing more rows than planned.

2019-12-26