CREATE UNIQUE INDEX terminated because a duplicate key was found
You make a column unique on a table that has been in use for a while:
CREATE UNIQUE INDEX UX_Account_Email ON dbo.Account (Email);
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name
'dbo.Account' and the index name 'UX_Account_Email'. The duplicate key value is (a@x.com).
The message names exactly one value. It is the first collision found, not a report — there may be one more or four hundred, and fixing the named one just gets you the next message.
A unique constraint fails the same way, with an extra line, because a constraint is an index underneath:
ALTER TABLE dbo.Account ADD CONSTRAINT UQ_Account_Email UNIQUE (Email);
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found ...
Msg 1750, Level 16, State 1, Line 1
Could not create constraint or index. See previous errors.
Two nulls are a duplicate
This is the part that surprises people, and it is why the message so often reads
The duplicate key value is (<NULL>):
INSERT dbo.Account VALUES (4, NULL), (5, NULL);
Two rows with no email at all will defeat a unique index. In comparisons, NULL = NULL is unknown;
for a unique index, SQL Server treats nulls as equal to each other, so exactly one row may have no
value. That is the opposite of what most people expect from "the email must be unique", where the
intention is nearly always unique when there is one.
The fix for that case is a filtered index:
SET QUOTED_IDENTIFIER ON;
CREATE UNIQUE INDEX UX_Account_Email ON dbo.Account (Email) WHERE Email IS NOT NULL;
Now any number of rows may have no email, and the ones that do have one must differ.
SET QUOTED_IDENTIFIER ON matters. It is on by default in SQL Server Management Studio and in
most application connections, which is why this is rarely seen interactively — but it is off by
default in sqlcmd, so a migration script run that way fails on the filtered index and only on
the filtered index:
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Put the SET at the top of the script and it applies to the whole batch.
List every duplicate, not just the first
SELECT Email, COUNT(*) AS Rows_, MIN(AccountId) AS KeepThisOne
FROM dbo.Account
WHERE Email IS NOT NULL
GROUP BY Email
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;
Email Rows_ KeepThisOne
--------- ----- -----------
a@x.com 2 1
Leave the WHERE Email IS NOT NULL out if you are building an unfiltered index and need the nulls
counted too.
To see the whole rows rather than the values — usually what you need, because the decision is which row survives:
WITH Duplicated AS
(
SELECT *, COUNT(*) OVER (PARTITION BY Email) AS CopiesOfThisEmail
FROM dbo.Account
WHERE Email IS NOT NULL
)
SELECT *
FROM Duplicated
WHERE CopiesOfThisEmail > 1
ORDER BY Email, AccountId;
Choosing what to keep
"Keep the lowest id" is the usual reflex and it is often wrong. The oldest row may be an abandoned signup while the newest is the account in daily use. Before deleting anything, ask what points at these rows:
SELECT OBJECT_NAME(parent_object_id) AS ReferencingTable, name AS ForeignKey
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('dbo.Account');
Every table in that list has rows pointing at an account id you are about to remove, and they need repointing at whichever row you keep — before the delete, or the delete fails on the foreign key.
When the duplicates are genuinely the same thing and nothing references them, this deletes all but one of each:
WITH Ranked AS
(
SELECT AccountId, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY AccountId) AS Copy
FROM dbo.Account
WHERE Email IS NOT NULL
)
DELETE FROM Ranked WHERE Copy > 1;
Run it as a SELECT first. ORDER BY AccountId is the decision about which copy survives, and it
is the only line in there worth arguing about.
A gentler order of work
Adding the index last is the reflex, and it means the cleanup and the constraint are the same deployment. The alternative is to add the filtered unique index first against the rows that are already clean, which stops the duplicates growing while you decide what to do with the ones you have.
Seeing it before you run it
What makes this change expensive is never the index; it is the rows pointing at the ones you are about to merge, in tables you did not know were involved.
WoodFireERD draws that for you — pick the table, follow what points at it, and take the change out as an ordered script with the index, the constraint and the clean-up steps in the order they have to run.