SQL Server Error 2627

Why it happens

Error 2627 means you tried to write a row whose key value already exists in a column protected by a PRIMARY KEY or UNIQUE constraint. Those constraints exist precisely to reject duplicates, so SQL Server stops the statement and rolls it back rather than store two rows with the same key.

The message names the constraint, the table, and the exact duplicate value: The duplicate key value is (1). That value is your fastest clue: it tells you which row already exists. The error is severity 14, which is a normal, catchable constraint violation, not a server problem.

Common triggers are inserting a record twice (a double submit or a re-run script), supplying an explicit id that is already taken, an UPDATE that changes a key into one another row already holds, or an INSERT ... SELECT whose source contains duplicates.

Examples

Inserting a duplicate primary key

CREATE TABLE dbo.Customers (
    id   INT PRIMARY KEY,
    name NVARCHAR(100)
);

INSERT INTO dbo.Customers (id, name) VALUES (1, 'Sara');
INSERT INTO dbo.Customers (id, name) VALUES (1, 'Ali');  -- id 1 already exists

Msg 2627, Level 14, State 1, Line 8
Violation of PRIMARY KEY constraint 'PK_Customers'.
Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (1).

An UPDATE that creates a duplicate

-- Two rows exist with id 1 and id 2.
-- Changing id 2 to 1 collides with the existing row.
UPDATE dbo.Customers SET id = 1 WHERE id = 2;

Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK_Customers'. The duplicate key value is (1).

Duplicates inside an INSERT ... SELECT

INSERT INTO dbo.Customers (id, name)
SELECT id, name FROM staging.Customers;  -- staging has two rows with id 5

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Customers'. The duplicate key value is (5).

How to fix it

IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE id = 1)
    INSERT INTO dbo.Customers (id, name) VALUES (1, 'Sara');

MERGE dbo.Customers AS t
USING (SELECT 1 AS id, 'Sara' AS name) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

CREATE TABLE dbo.Customers (
    id   INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100)
);

-- Do not pass id; SQL Server assigns it
INSERT INTO dbo.Customers (name) VALUES ('Sara');

INSERT INTO dbo.Customers (id, name)
SELECT id, name
FROM (
    SELECT id, name,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) AS rn
    FROM staging.Customers
) d
WHERE rn = 1;

How to prevent it

Use an IDENTITY column or a SEQUENCE for surrogate primary keys so the database, not the application, guarantees uniqueness. This removes the most common cause of 2627 outright.

Make writes idempotent: use IF NOT EXISTS or MERGE for inserts that might run twice, and validate keys in the application before submitting. De-duplicate any staging or import data before loading it. For details on choosing keys, see primary key vs foreign key and the constraints guide.

Common questions