Any suggestions? Export (0) Print
Expand All


Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Product Name

SQL Server

Event ID


Event Source




Symbolic Name


Message Text

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name '%.*ls' and index name '%.*ls'. The duplicate key value is %ls.

This error occurs when you attempt to create a unique index and more than one row in the table contains the specified duplicate value. A unique index is created when you create an index and specify the UNIQUE keyword, or when you create a UNIQUE constraint. The table cannot contain any rows that have duplicate values in the columns defined in the index or constraint.

Consider the data in the following Employee table:







Senior Tool Designer




Marketing Assistant




Design Engineer




Tool Designer


A unique index can not be created on the column combinations LastName or LastName, FirstName because of duplicate values in the rows.

Less obvious is the potential for a uniqueness violation in the HireDate column. For indexing purposes, NULL values compare as equal. Therefore, you cannot create a unique index or constraint if the key values are NULL in more than one row. Given the data above, a unique index cannot be created on the column combinations HireDate or LastName, HireDate.

Error message 1505 returns the first row that violates the uniqueness constraint. There may be other duplicate rows in the table. To find all duplicate rows, query the specified table and use the GROUP BY and HAVING clauses to report the duplicate rows. For example, the following query returns the rows in the Employee table that have duplicate first and last names.

SELECT LastName, FirstName, count(*) FROM dbo.Employee GROUP BY LastName, FirstName HAVING count(*) > 1;

Consider the following solutions.

  • Add or remove columns in the index or constraint definition to create a unique composite. In the previous example, adding a MiddleName column to the index or constraint definition might resolve the duplication problem.

  • Select columns that are defined as NOT NULL when you choose columns for a unique index or constraint. This eliminates the possibility of a uniqueness violation caused when more than one row contains NULL in the key values.

  • If the duplicate values are the result of data entry errors, manually correct the data and then create the index or constraint. For information about removing duplicate rows in a table, see Knowledge Base article 139444: How to remove duplicate rows from a table in SQL Server.

Community Additions

© 2016 Microsoft