Violation of PRIMARY KEY Constraint and Cannot Insert Duplicate Key (Errors 2627 and 2601)

Cannot insert duplicate key row in object 'dbo.ErrDemoPk' with unique index
'UX_ErrDemoPk_v'. The duplicate key value is (10).
Msg 2601, Level 14, State 1

Same problem, two enforcement points. 2627 is a constraint saying no (primary key or unique constraint), 2601 is a unique index saying no. The row you tried to insert or update collides with a key value that is already in the table, and SQL Server tells you the exact value in the message, which is the single most useful thing about this error and the first thing to use.

Why This Happens

The engine is doing its job. Something upstream sent a key value twice:

• Retry logic that is not idempotent. The app timed out, retried, and the first attempt had actually committed. The second insert is the duplicate.
• An ETL batch loaded twice, or a load that failed halfway was rerun from the top without clearing what already landed.
• A natural key that is not as unique as everyone believed. Customer references, invoice numbers and email addresses all collide eventually.
• Concurrent inserts racing on check-then-insert code: two sessions both check the key is absent, both insert, one gets 2627.

Start With the Value in the Message

The message hands you the colliding key. Go and look at what already owns it:

SELECT *
FROM dbo.ErrDemoPk
WHERE id = 1;   -- the duplicate key value from the message

Half the time this settles it immediately: the existing row is the same business record and the insert was a replay. The other half, the existing row is a different record and the real problem is the key design.

Then check the source for duplicates before reloading anything:

SELECT v, COUNT(*) AS occurrences
FROM staging.IncomingRows
GROUP BY v
HAVING COUNT(*) > 1;

The Fixes, By Cause

Replayed inserts: make the write idempotent rather than deleting the constraint’s protection. The simplest safe pattern:

INSERT INTO dbo.ErrDemoPk (id, v)
SELECT @id, @v
WHERE NOT EXISTS (SELECT 1 FROM dbo.ErrDemoPk WITH (UPDLOCK, HOLDLOCK) WHERE id = @id);

The locking hints matter: without them two concurrent runs can both pass the NOT EXISTS check and you are back where you started.

Double-loaded batches: dedupe in staging with the GROUP BY above, or load through a query that takes one row per key (ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) = 1).

A key that is not really unique: that is a design conversation, not an error fix. Widening the key or moving to a surrogate changes the table’s contract, so it belongs in change control, not in the incident.

What not to do: IGNORE_DUP_KEY silently discards duplicate rows instead of erroring. On a unique index it turns 2601 into a warning and throws the data away, which converts a loud, diagnosable failure into quiet data loss. It has legitimate uses in controlled staging loads; on a production table that other people insert into, it hides exactly the signal this error exists to give you.

Telling the Two Apart in Practice

You rarely need to. The investigation is identical, the message names the object either way, and the only operational difference is where the rule lives: drop-and-recreate scripts handle a constraint (2627) through ALTER TABLE and an index (2601) through DROP INDEX. If you are seeing 2601 on what you thought was a plain primary key, someone also built a separate unique index, and sp_helpindex 'dbo.ErrDemoPk' will show you the full picture.

Common Questions

Related Scripts

• Get Index Design Issues, key and index design problems before they error
• Get Duplicate Indexes, when the same rule is enforced twice
• Lock Request Timeout (1222), what the race-closing locking hints can surface instead