Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

What it means

A primary key, a UNIQUE constraint and a unique index each allow a value (or a combination of values) only once. Your INSERT, UPDATE or MERGE would have created a second row with the same key, so SQL Server stopped the statement and undid all of it: none of its rows were written.

The number tells you which kind of object refused, and both messages end with the clashing value:

Only the statement fails. If you’re inside a transaction, it stays open and the earlier statements in it can still be committed, unless SET XACT_ABORT ON is in effect: then the whole transaction is rolled back.

Common causes

1. A real duplicate: the same email signed up twice, a retried request, a job that ran twice, or the same key twice in one batch of rows you’re loading.
2. Check-then-insert under load. IF NOT EXISTS (SELECT …) INSERT … isn’t atomic: two sessions both find no row, and both insert.
3. The id generator is behind the data. The duplicate value is an id you didn’t supply. Someone ran DBCC CHECKIDENT (…, RESEED, 0) after deleting rows, or the id comes from a SEQUENCE default and rows were loaded with explicit ids, which a sequence never notices. Code that picks MAX(id) + 1 itself clashes as soon as two sessions run it at once.
4. An upsert written as a plain insert, or a MERGE whose source has the same key twice.
5. A second NULL in a unique column. SQL Server allows only one NULL in a UNIQUE constraint or unique index, unlike PostgreSQL and MySQL; the message then reads The duplicate key value is (<NULL>).

Adding a unique constraint or index to data that already has duplicates fails with a different number, 1505 (The CREATE UNIQUE INDEX statement terminated because a duplicate key was found…).

How to fix it

Find the duplicates

The message names the constraint or index and the value. To list every clash before you add a constraint or load data:

SELECT email, COUNT(*) AS copies
FROM dbo.customers
GROUP BY email
HAVING COUNT(*) > 1;

Check the IDENTITY or sequence

NORESEED only reports; it changes nothing:

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

Checking identity information: current identity value '1', current column value '2'.

When the current identity value is below the highest id in the column, RESEED with no value moves it up to that maximum:

DBCC CHECKIDENT ('dbo.plans', RESEED);

It needs db_owner, db_ddladmin, sysadmin or ownership of the schema. Never reseed below the highest id to “close gaps”: that is how the clash starts. Failed inserts use up identity values too, so gaps are normal.

Inserting explicit ids with SET IDENTITY_INSERT dbo.plans ON moves the IDENTITY past them by itself. A SEQUENCE doesn’t: after loading rows with their own ids, restart it past the highest one.

SELECT current_value FROM sys.sequences WHERE name = 'order_ids';
SELECT MAX(id) FROM dbo.orders;
ALTER SEQUENCE dbo.order_ids RESTART WITH 3;   -- MAX(id) + 1

Make insert-or-update safe

Hold the lock from the check until the insert, inside one transaction. UPDLOCK, HOLDLOCK makes a second session wait at the SELECT until the first commits, and then it sees the row:

BEGIN TRAN;
IF NOT EXISTS (SELECT 1 FROM dbo.customers WITH (UPDLOCK, HOLDLOCK) WHERE email = @email)
  INSERT INTO dbo.customers (email, name) VALUES (@email, @name);
ELSE
  UPDATE dbo.customers SET name = @name WHERE email = @email;
COMMIT;

Or use MERGE with HOLDLOCK, which Microsoft’s documentation says protects against unique key violations when keys are both inserted and updated:

MERGE dbo.customers WITH (HOLDLOCK) AS t
USING (VALUES (@email, @name)) AS s (email, name)
  ON t.email = s.email
WHEN MATCHED THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT (email, name) VALUES (s.email, s.name);

MERGE compares each source row with the table as it was, so a source with the same new key twice inserts it twice and fails with 2627. Remove duplicates from the source first (GROUP BY, or keep ROW_NUMBER() = 1 per key).

To add only the rows that are missing, filter them in the INSERT:

INSERT INTO dbo.customers (email, name)
SELECT s.email, s.name
FROM staging.customers AS s
WHERE NOT EXISTS (SELECT 1 FROM dbo.customers AS c WITH (UPDLOCK, HOLDLOCK) WHERE c.email = s.email);

Skip duplicates on purpose: IGNORE_DUP_KEY

A unique index created WITH (IGNORE_DUP_KEY = ON) drops the duplicate rows of an INSERT with a warning (Duplicate key was ignored.) and inserts the rest. It applies to inserts only, not to UPDATE, and can’t be used on a filtered index. It suits a staging table you’re loading; on a real table it hides the duplicates you may want to know about.

CREATE UNIQUE INDEX IX_tags_tag ON dbo.tags (tag) WITH (IGNORE_DUP_KEY = ON);

Allow many NULLs with a filtered index

To keep values unique but allow any number of NULLs, replace the constraint with a filtered unique index:

ALTER TABLE dbo.members DROP CONSTRAINT UQ_members_phone;
CREATE UNIQUE INDEX UX_members_phone ON dbo.members (phone) WHERE phone IS NOT NULL;

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, in a scratch schema. A UNIQUE constraint on email and a unique index on name:

Msg 2627, Level 14, State 1, Server 7732422b7f56, Line 1
Violation of UNIQUE KEY constraint 'UQ_customers_email'. Cannot insert duplicate key in object 'seo_sqlerr_data.customers'. The duplicate key value is (ada@example.com).
The statement has been terminated.
Msg 2601, Level 14, State 1, Server 7732422b7f56, Line 1
Cannot insert duplicate key row in object 'seo_sqlerr_data.customers' with unique index 'IX_customers_name'. The duplicate key value is (Ada).
The statement has been terminated.

A table with id int IDENTITY(1,1). Ids 1 and 2 loaded with IDENTITY_INSERT ON moved the identity to 2, and the next insert got 3. Then id 3 was deleted and the table “reset”:

DBCC CHECKIDENT ('seo_sqlerr_data.plans', RESEED, 0);
INSERT INTO seo_sqlerr_data.plans (name) VALUES (N'team');

Msg 2627, Level 14, State 1, Server 7732422b7f56, Line 1
Violation of PRIMARY KEY constraint 'PK_plans'. Cannot insert duplicate key in object 'seo_sqlerr_data.plans'. The duplicate key value is (1).
The statement has been terminated.

DBCC CHECKIDENT … NORESEED then reported current identity value '1', current column value '2' (the failed insert used up 1). After DBCC CHECKIDENT ('seo_sqlerr_data.plans', RESEED) it reported '2', '2', and the insert returned id 3. A table whose id defaults to NEXT VALUE FOR a sequence, after rows 1 and 2 were inserted with explicit ids, failed the same way on (1) and worked after ALTER SEQUENCE … RESTART WITH 3.

Two sessions running the check-then-insert at once (a two-second WAITFOR between the check and the insert): the second got 2627 on lin@example.com. With WITH (UPDLOCK, HOLDLOCK) on the check, the second session waited and neither failed. A second NULL in a UNIQUE column:

Msg 2627, Level 14, State 1, Server 7732422b7f56, Line 1
Violation of UNIQUE KEY constraint 'UQ_members_phone'. Cannot insert duplicate key in object 'seo_sqlerr_data.members'. The duplicate key value is (<NULL>).
The statement has been terminated.

With the filtered index instead, two NULLs went in, and a repeated phone number got 2601. Unlike PostgreSQL, SQL Server checks uniqueness at the end of the statement, so UPDATE seats SET seat_no = seat_no + 1 on seats 1 and 2 worked. With SET XACT_ABORT ON, a 2627 inside a transaction rolled back the rows inserted before it; without it, they were committed. SQL Server 2019 and 2025 printed the same messages.

In Inlet

Grid edits are staged until you commit (⌘S), and Review shows what will run first, so you can spot a key you’re about to repeat. When SQL Server refuses, Inlet shows its message with SQL Server error 2627, state 1, severity 14 and links to this page. A table’s definition (its CREATE TABLE with constraints and indexes) shows which columns UQ_customers_email covers. With your own Anthropic API key, Ask Claude (⌘L) can write the MERGE for your table; it sends the schema, never rows.