SQL Server error 2601: Cannot insert duplicate key row
An INSERT or UPDATE would put a duplicate value into a unique index. The message names the table, the index and the duplicate key value.
- Error number
2601- Severity
- 14
- Category
- Constraints and keys
What it means
Error 2601 is raised by a unique index created with CREATE UNIQUE INDEX, including filtered unique indexes. It is the index counterpart of 2627, which comes from PRIMARY KEY and UNIQUE constraints. The statement stops and its changes are rolled back.
Common causes
- Writing a value that another row already has in a column covered by a unique index.
- A unique index on a nullable column: SQL Server treats NULLs as equal in unique indexes, so only one row may have NULL unless the index is filtered.
- A load whose source contains duplicates.
How to fix it
-
Find the conflicting row with the value from the message, then update it or skip the new one.
SELECT * FROM dbo.Users WHERE UserName = N'ada';
-
To allow many NULLs but keep non-NULL values unique, make the index filtered.
DROP INDEX UX_Users_TaxId ON dbo.Users; CREATE UNIQUE INDEX UX_Users_TaxId ON dbo.Users (TaxId) WHERE TaxId IS NOT NULL;
-
Check which columns the index covers.
EXEC sp_helpindex 'dbo.Users';
Reproduce it safely
CREATE TABLE #u (v int NULL); CREATE UNIQUE INDEX ux ON #u (v); INSERT INTO #u VALUES (NULL); INSERT INTO #u VALUES (NULL); -- Msg 2601: The duplicate key value is (<NULL>). DROP TABLE #u;
Questions
Why can't I insert a second NULL into a unique column?
In SQL Server a unique index or constraint treats NULL as a value, so only one NULL is allowed. A filtered unique index with WHERE column IS NOT NULL allows any number of NULLs.
Related errors
2627Violation of PRIMARY KEY or UNIQUE KEY1913Index or statistics name already exists23505PostgreSQL: duplicate key violates unique constraint1062MySQL: Duplicate entry for keyORA-00001Oracle: unique constraint violated
More constraints and keys errors
547Statement conflicted with a constraint515Cannot insert the value NULL into column3726Can't drop: referenced by a FOREIGN KEY1750Could 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.