Error 1505

Severity Level 14

Message Text

Create unique index aborted on duplicate key. Primary key is '%S_KEY'

Explanation

This error occurs when you attempt to create a unique index and more than one row contains the duplicate value. SQL Server has detected a uniqueness violation and cancels the create index operation.

For example, the creation for the index below would be canceled due to the uniqueness violation at row 3. The DORR ROBERT combination is already located in row 1.

TABLE:

tblTest

Row

strLastName

strFirstName

strCity

strState

1

DORR

ROBERT

IRVING

TX

2

DEDECKER

TODD

LEWISVILLE

TX

3

DORR

ROBERT

PLANO

TX

4

SCOTT

HULKE

PLANO

TX

create unique nonclustered index
idxUniqueNames ON
tblText(strLastName, strFirstName)

Action

You must review your index objective. If your index does not need to be unique, remove the UNIQUE keyword and reissue the create index statement. However, if you still want to create a unique index, you must query the table in question and remove the rows in error. For more detailed information on the create index statement, see the Microsoft SQL Server Transact-SQL Reference.

To find the duplicate rows, issue a GROUP BY statement:

SELECT * FROM tblTest
GROUP BY strLastName, strFirstName