// SQL Server error reference

SQL Server error 1505: Duplicate key found, unique index

A unique index or constraint can't be created because the table already contains duplicate values in those columns. The message shows one of them.

Error number
1505
Severity
16
Category
Constraints and keys
Error message
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name '%.*ls' and the index name '%.*ls'. The duplicate key value is %ls.

What it means

Before a unique index or a primary key or unique constraint can be built, every existing row must have a distinct key. If the data already contains duplicates, the build stops with 1505 and shows one duplicate value. Error 1750 usually follows when the index was part of a constraint.

Common causes

  • Duplicate rows that crept in before the uniqueness rule was added.
  • Multiple NULLs in a column you're making unique (SQL Server counts NULLs as equal in unique indexes).

How to fix it

  1. Find all duplicate keys.

    SELECT Email, COUNT(*) AS n FROM dbo.Customers GROUP BY Email HAVING COUNT(*) > 1;
  2. Remove or merge duplicates, keeping one row per key.

    WITH d AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerId) AS rn FROM dbo.Customers
    )
    DELETE FROM d WHERE rn > 1;
  3. If many NULLs should be allowed, create a filtered unique index (WHERE Email IS NOT NULL).

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.