Home/Error Library/sqlserver-2627-violation-primary-key-unique-constraint

SQL Server

SQL Server 2017 - 2022Data & Constraints

3 min read

Msg 2627, Level 14: Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'.

SQL Server Primary Key & Unique Constraint Violations: IDENTITY Reseed Drift, Concurrency & Idempotent Ingestion

Root causes behind duplicate key errors during bulk loading and concurrent inserts; DBCC CHECKIDENT diagnostics and atomic SEQUENCE patterns.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: SQL Server 2017 / 2019 / 2022

Operational Verification & Reliability Metadata

Last Technical Review: 2 Kasım 2026

Tested VersionsSQL Server 2019 CU27, 2022 CU14

Required Privilegesdb_datawriter or ALTER on table

Risk Assessment

MediumIncorrectly seeding identity counters breaks future sequential insert operations

Expected OutputDBCC CHECKIDENT resetting identity seed above maximum existing key

Rollback Procedure:Reset identity seed safely above MAX(id)

Microsoft Docs: DBCC CHECKIDENT

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

Application insert transactions abort with:

plaintext

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'. The duplicate key value is (1052).
The statement has been terminated.

▸What You See in Production

Inserts fail even on auto-incrementing identity columns because the engine generates identity values colliding with existing primary keys.

▸Root Cause Matrix

Probability	Root Cause	Description
%50	IDENTITY Reseed Drift	Following bulk inserts (`IDENTITY_INSERT ON`) or ungraceful failovers, identity seed lags behind `MAX(id)`.
%35	Concurrent Unique Collision	Interleaved transactions inserting duplicate unique domain keys.
%15	Unprotected MERGE Statements	`MERGE` statements running without concurrency hints (`HOLDLOCK`).

▸EMERGENCY RUNBOOK (0-5 min)

sql

-- 1. Inspect existing duplicate record
SELECT * FROM dbo.Users WHERE id = 1052;

-- 2. Inspect maximum ID versus identity counter
SELECT MAX(id) AS MaxID FROM dbo.Users;
DBCC CHECKIDENT ('dbo.Users', NORESEED);

-- 3. Auto-align identity seed above existing records
DBCC CHECKIDENT ('dbo.Users', RESEED);

▸PERMANENT BLUEPRINT

1MERGE Concurrency Hardening: Always append WITH (HOLDLOCK) to target tables in MERGE logic.
2SEQUENCE Objects: Adopt cached SEQUENCE structures for high-throughput tables.

▸Verification

sql

DBCC CHECKIDENT ('dbo.Users', NORESEED);

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (82)

SQLSERVER5 min