If you’re getting an error that reads something like “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” it’s probably because you’re trying to create a UNIQUE constraint on a column that already contains duplicate values.

We can’t apply a UNIQUE constraint to a column that already contains duplicates, as it defies the logic of applying the UNIQUE constraint in the first place.

The most obvious solution to this issue is to change the data so that there are no duplicates.

But if that’s not an option, then there is a workaround that we can use in order to get the UNIQUE constraint added without having to change any data.

Example of Error

Suppose we have a table called Employees with the following data:

EmployeeID  FirstName  LastName  Email         
----------  ---------  --------  --------------
100         Homer      Simpson   [email protected]  
200         Bart       Simpson   [email protected]
300         Marge      Simpson   [email protected]  
400         Peter      Griffin   [email protected]  
500         Felix      Heckler   [email protected]

We can see that Homer and Marge share the same email address: [email protected]

Here’s an example of code that produces the 1505 error:

ALTER TABLE Employees
ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email);

Output:

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.Employees' and the index name 'UQ_Employees_Email'. The duplicate key value is ([email protected]).
Msg 1750, Level 16, State 1, Line 1
Could not create constraint or index. See previous errors.

It tells us pretty much everything we need to know – the constraint couldn’t be created because a duplicate key was found ([email protected]).

Actually, the error message mentions a CREATE UNIQUE INDEX statement even though we didn’t use that statement. SQL Server creates a UNIQUE index whenever we create a UNIQUE constraint, and so this is probably what it’s referring to.

Solution

The most obvious solution is to remove any duplicate values from the column. But if that’s not an option, then we do have another option.

We can add a filtered index, which is where we add a UNIQUE index, but filter out certain rows.

For example, the following code can be used to add a UNIQUE index, but only for new records:

CREATE UNIQUE INDEX UX_Employees_Email 
ON Employees(Email) 
WHERE EmployeeID > 300;

Here, we filtered out any rows with an EmployeeId of 300 or lower. We used this value because that’s the highest value of the duplicate value (it’s Marge’s ID). This assumes that the EmployeeId column increments sequentially, and that all future inserts will be higher than 300. However, if a future insert is less than that, it won’t be a problem, as far as the UNIQUE index is concerned, but it just won’t be enforced for duplicates.

If there are just a handful of duplicates scattered throughout many rows, you could try specifying them explicitly in the query.

If you’re wondering why we’re creating an index instead of a constraint, remember that when we add a UNIQUE constraint, SQL Server does this through a UNIQUE index. And so when we use CREATE UNIQUE INDEX, we’re effectively creating a UNIQUE constraint.