// SQL Server error reference

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
Error message
Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

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

  1. 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';
  2. 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;
  3. 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

More constraints and keys errors

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.

// fewer errors, faster fixes

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.