SQL Server error 2627: Violation of PRIMARY KEY or UNIQUE KEY
An INSERT or UPDATE would duplicate a value in a PRIMARY KEY or UNIQUE constraint. The message names the constraint, table and duplicate key value.
- Error number
2627- Severity
- 14
- Category
- Constraints and keys
What it means
Primary key and unique constraints guarantee that no two rows share the same key. When a write would create a duplicate, SQL Server stops the statement with 2627, followed by 3621 ("The statement has been terminated"). The message says which kind of constraint (PRIMARY KEY or UNIQUE KEY), its name, the table, and since SQL Server 2017 the duplicate value itself.
Error 2601 is the same failure on a unique index that is not a constraint.
Common causes
- Inserting a row whose key already exists, for example re-running a load.
- Inserting explicit values into an identity column with
IDENTITY_INSERTon, or an identity seed reset below existing values. - A batch insert from a source that itself contains duplicate keys.
- Two sessions inserting the same key concurrently: the check-then-insert pattern (
IF NOT EXISTS ... INSERT) isn't safe without locking hints.
How to fix it
-
Find the existing row using the value shown in the message.
SELECT * FROM dbo.Customers WHERE Email = N'[email protected]';
-
Skip rows that already exist when loading.
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 WHERE c.Email = s.Email);
-
Look for duplicates inside the source before inserting.
SELECT Email, COUNT(*) FROM staging.Customers GROUP BY Email HAVING COUNT(*) > 1;
-
For an upsert that is safe under concurrency, update first and insert if nothing was updated, holding a range lock.
BEGIN TRANSACTION; UPDATE dbo.Customers WITH (UPDLOCK, SERIALIZABLE) SET Name = @Name WHERE Email = @Email; IF @@ROWCOUNT = 0 INSERT INTO dbo.Customers (Email, Name) VALUES (@Email, @Name); COMMIT; -
If an identity column collides, check and reseed it.
DBCC CHECKIDENT ('dbo.Customers', NORESEED); -- shows current and max values DBCC CHECKIDENT ('dbo.Customers', RESEED);
Reproduce it safely
CREATE TABLE #k (id int PRIMARY KEY); INSERT INTO #k VALUES (1); INSERT INTO #k VALUES (1); -- Msg 2627 ... The duplicate key value is (1). DROP TABLE #k;
Questions
What is the difference between error 2627 and 2601?
2627 is raised by a PRIMARY KEY or UNIQUE constraint; 2601 by a unique index created with CREATE UNIQUE INDEX. The cause and the fixes are the same.
Can I make SQL Server ignore duplicate keys?
A unique index created WITH (IGNORE_DUP_KEY = ON) discards duplicate rows in an INSERT with a warning instead of failing the statement. Use it deliberately, since it silently drops data.
Related errors
2601Cannot insert duplicate key row547Statement conflicted with a constraint515Cannot insert the value NULL into column23505PostgreSQL: duplicate key violates unique constraint1062MySQL: Duplicate entry for keyORA-00001Oracle: unique constraint violatedSQLITE_CONSTRAINT_PRIMARYKEYSQLite: PRIMARY KEY constraint failed
More constraints and keys errors
3726Can't drop: referenced by a FOREIGN KEY1913Index or statistics name already exists1750Could not create constraint or index4712Can't truncate a referenced table1505Duplicate key found, unique index
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.