SQL Server: 'Violation of PRIMARY KEY constraint. Cannot insert duplicate key' (error 2627)

Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (1). (error 2627) means a row with that key is already in the table. The message names the constraint and the value, so look that row up, then decide whether the insert should have been an update or should not have chosen the key at all.

For new rows, let SQL Server pick the key with an IDENTITY column and read it back with OUTPUT inserted.Id. For "insert if missing" and re-runnable seed scripts, guard the insert with IF NOT EXISTS or use MERGE, both shown below.

The error

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "INSERT INTO dbo.Customers (Id, Name, Email) VALUES (1, 'Aiko Tanaka', 'aiko.tanaka@example.test')"
ex.Number = 2627, ex.Class = 14, ex.State = 1
Microsoft.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (1).
The statement has been terminated.

Why it happens

dbo.Customers (in the test database fix7_lab) had a plain INT primary key, and the application chose the Ids itself. Id 1 already belonged to Maria Garcia, so the second row with Id 1 was refused and the whole statement was rolled back. Hand-picked keys collide in exactly this way: a seed script run twice, two users computing MAX(Id) + 1 at the same moment, or an import that carries the old system's Ids.

Its sibling, error 2601, is the same refusal from a unique index rather than a constraint. Which number you get depends only on how the uniqueness was declared:

ex.Number = 2601, ex.Class = 14, ex.State = 1
Microsoft.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.Customers' with unique index 'UX_Customers_Email'. The duplicate key value is (maria.garcia@example.test).
The statement has been terminated.

After replacing that index with CONSTRAINT UQ_Customers_Email UNIQUE (Email), the same duplicate came back as 2627 with "Violation of UNIQUE KEY constraint". Code that catches duplicates should test for both numbers. EF Core wraps either one: SaveChanges threw Microsoft.EntityFrameworkCore.DbUpdateException ("An error occurred while saving the entity changes. See the inner exception for details.") with the 2627 SqlException as its inner exception.

The fix

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "IF NOT EXISTS (SELECT 1 FROM dbo.Customers WITH (UPDLOCK, HOLDLOCK) WHERE Id = 1) INSERT INTO dbo.Customers (Id, Name, Email) VALUES (1, 'Maria Garcia', 'maria.garcia@example.test'); SELECT Id, Name, Email FROM dbo.Customers ORDER BY Id"
1 | Maria Garcia | maria.garcia@example.test
2 | David Chen | david.chen@example.test
OK (-1 row(s) affected) in 0.2 s

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "MERGE dbo.Customers WITH (HOLDLOCK) AS t USING (VALUES (2, 'David Chen', 'david.chen@example.test'), (3, 'Aiko Tanaka', 'aiko.tanaka@example.test')) AS s (Id, Name, Email) ON t.Id = s.Id WHEN MATCHED THEN UPDATE SET Name = s.Name, Email = s.Email WHEN NOT MATCHED THEN INSERT (Id, Name, Email) VALUES (s.Id, s.Name, s.Email); SELECT Id, Name, Email FROM dbo.Customers ORDER BY Id"
1 | Maria Garcia | maria.garcia@example.test
2 | David Chen | david.chen@example.test
3 | Aiko Tanaka | aiko.tanaka@example.test
OK (2 row(s) affected) in 0.2 s

The guarded insert skipped the existing row without an error; MERGE updated Id 2 and inserted Id 3. The lock hints are the usual guard against two sessions deciding at the same moment that the row is missing (that race was not part of this test). For rows that have no natural Id, the better fix is to stop choosing keys:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "INSERT INTO dbo.Customers (Name, Email) OUTPUT inserted.Id VALUES ('Aiko Tanaka', 'aiko.tanaka@example.test')"
3
OK (1 row(s) affected) in 0.2 s

That run was against the table recreated with Id INT IDENTITY(1,1), which already held two rows. Do not send an Id to an identity column, or you meet error 544.

How it was reproduced

A console app from dotnet new console -n FixLab -f net10.0 (.NET SDK 10.0.403) with Microsoft.Data.SqlClient 7.1.1, which runs the SQL in its second argument, against SQL Server 2022 LocalDB 16.0.1210.3. The EF Core check was a second console app on Microsoft.EntityFrameworkCore.SqlServer 10.0.14 (SqlClient 6.1.7) adding a customer with Id = 1 and ValueGeneratedNever().

Frequently asked

What is the difference between SQL Server error 2627 and 2601?

Both reject a duplicate value. 2627 comes from a PRIMARY KEY or UNIQUE constraint, 2601 from a unique index created with CREATE UNIQUE INDEX. Catch both numbers when you handle duplicates in code.

How do I insert a row only if it does not exist in SQL Server?

Wrap the insert in IF NOT EXISTS (SELECT 1 FROM table WITH (UPDLOCK, HOLDLOCK) WHERE key = value), or use MERGE WITH (HOLDLOCK) to update existing rows and insert new ones in one statement.

How do I catch a duplicate key error in EF Core?

SaveChanges throws DbUpdateException. Check whether its InnerException is a SqlException with Number 2627 or 2601, and treat that as a duplicate rather than a general failure.

PostgreSQL reports the same mistake as 23505 after seeding rows. More decoded errors in the Fixes category.