SQL Server Error 2627: Violation of PRIMARY KEY Constraint (Fix Guide)

The Error Message

SQL Server gives you everything you need in this message: the constraint name (PK_Orders), the table (dbo.Orders), and — crucially — the exact duplicate value (1001). Start every investigation by looking up that value in the table. The same wording with "UNIQUE KEY constraint" means a UNIQUE constraint rather than the primary key; both are error 2627.

What Causes Error 2627?

An INSERT (or an UPDATE that changes a key column) tried to write a value that already exists under a PRIMARY KEY or UNIQUE constraint. The data, not the syntax, is the problem — your statement is valid, but executing it would create two rows with the same key.

Common Causes (Ranked)

Quick Diagnosis

The error told you the value. Now find the existing row and the constraint definition:

-- 1. Look at the row that already owns the key:
SELECT * FROM dbo.Orders WHERE OrderID = 1001;

-- 2. Which columns does the violated constraint cover?
SELECT i.name AS constraint_name, c.name AS column_name
FROM   sys.indexes i
JOIN   sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN   sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE  i.name = 'PK_Orders';

-- 3. Bulk load failing? Find duplicates inside the SOURCE first:
SELECT OrderID, COUNT(*)
FROM   staging.Orders
GROUP  BY OrderID
HAVING COUNT(*) > 1;

-- 4. Suspect a bad identity seed? Compare seed vs. actual max:
SELECT IDENT_CURRENT('dbo.Orders') AS current_identity,
       MAX(OrderID) AS max_in_table
FROM   dbo.Orders;

How to Fix It

Fix 1: Insert only rows that don't exist yet

-- BAD: blind insert, fails if 1001 exists
INSERT INTO dbo.Orders (OrderID, CustomerID, Total)
VALUES (1001, 42, 99.90);
-- Msg 2627: Violation of PRIMARY KEY constraint 'PK_Orders'

-- FIXED: skip existing keys
INSERT INTO dbo.Orders (OrderID, CustomerID, Total)
SELECT 1001, 42, 99.90
WHERE  NOT EXISTS (SELECT 1 FROM dbo.Orders WHERE OrderID = 1001);

Fix 2: Upsert safely under concurrency

SQL Server has no ON DUPLICATE KEY UPDATE (MySQL) or ON CONFLICT (PostgreSQL). The concurrency-safe T-SQL pattern is update-then-insert with locking hints in one transaction:

-- BAD: unhinted IF EXISTS is a race — two sessions can both pass the check
IF NOT EXISTS (SELECT 1 FROM dbo.Orders WHERE OrderID = @id)
    INSERT INTO dbo.Orders (OrderID, CustomerID, Total) VALUES (@id, @cust, @total);

-- FIXED: serialize access to the key range
BEGIN TRAN;
UPDATE dbo.Orders WITH (UPDLOCK, HOLDLOCK)
SET    CustomerID = @cust, Total = @total
WHERE  OrderID = @id;

IF @@ROWCOUNT = 0
    INSERT INTO dbo.Orders (OrderID, CustomerID, Total)
    VALUES (@id, @cust, @total);
COMMIT;

Fix 3: MERGE for set-based upserts

For loading a batch from staging, MERGE handles insert-or-update in one statement. Add HOLDLOCK — MERGE alone is not atomic against concurrent inserts of the same key:

MERGE dbo.Orders WITH (HOLDLOCK) AS target
USING staging.Orders AS source
   ON target.OrderID = source.OrderID
WHEN MATCHED THEN
    UPDATE SET CustomerID = source.CustomerID, Total = source.Total
WHEN NOT MATCHED THEN
    INSERT (OrderID, CustomerID, Total)
    VALUES (source.OrderID, source.CustomerID, source.Total);

If the source itself contains the same key twice, MERGE fails with a different error ("The MERGE statement attempted to UPDATE or DELETE the same row more than once") — deduplicate the source first with ROW_NUMBER().

Fix 4: IGNORE_DUP_KEY for fire-and-forget dedup

For logging/staging tables where silently skipping duplicates is the desired behavior, set it on the unique index — duplicates then produce a warning, not an error:

CREATE UNIQUE INDEX UX_Events_EventKey
ON dbo.Events (EventKey)
WITH (IGNORE_DUP_KEY = ON);

-- Now duplicate rows are skipped:
-- "Duplicate key was ignored." (warning, statement succeeds)

Use sparingly: it discards data silently, which hides real bugs when duplicates were not supposed to happen.

Fix 5: Repair a broken identity seed

-- Identity below table max after a restore? Reseed to the max:
DECLARE @max BIGINT = (SELECT MAX(OrderID) FROM dbo.Orders);
DBCC CHECKIDENT ('dbo.Orders', RESEED, @max);
-- Next generated value will be @max + 1

ORM and Application Gotchas

• Entity Framework: attaching an entity with a key that already exists and calling Add instead of Update produces 2627 at SaveChanges. Inserting into IDENTITY tables with explicit IDs also requires SET IDENTITY_INSERT ON — and then it's on you to avoid collisions.
• Retry middleware: automatic retries (Polly, queue redelivery) re-run inserts that already committed. Make inserts idempotent — NOT EXISTS guard or upsert — before adding retry policies.
• Catch both 2627 and 2601: 2601 is the same duplicate situation raised by a unique index (created with CREATE UNIQUE INDEX) instead of a constraint. Error handlers that match only 2627 miss half the cases.
• GUID keys: switching to NEWID()/NEWSEQUENTIALID() defaults eliminates collision-by-generation entirely, at the cost of wider keys.

How to Prevent This Error

Let the database generate keys (IDENTITY, sequences, GUID defaults) instead of the application wherever possible. Make every insert that can be retried idempotent with an upsert pattern. Deduplicate staging data before loads. And treat 2627 in logs as a signal, not noise — frequent duplicates usually mean a retry loop or a double-submit bug upstream. Related reading: SQL Server Error 547: foreign key conflict (the other big constraint error) and MySQL Error 1062: duplicate entry if you work across both engines.

Fix SQL Server Errors with AI2SQL

Describe the load or upsert you need in plain English — AI2SQL generates concurrency-safe T-SQL with the right MERGE/NOT EXISTS pattern for your schema.

Frequently Asked Questions