Error 1505

Severity Level 14

Message Text

CREATE UNIQUE INDEX aborted because a duplicate key was found. Most significant 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. Microsoft® 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 BUCHANAN STEVEN combination is already located in row 1.

TABLE:     tblTest

Row      strLastName     strFirstName    strCity        strState

1        BUCHANAN        STEVEN          BOISE          ID

2        SUYAMA          MICHAEL         BUTTE          MT

3        BUCHANAN        STEVEN          SEATTLE        WA

4        DAVOLIO         NANCY           SAN FRANCISCO  CA

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 information about the CREATE INDEX statement, see CREATE INDEX.

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

SELECT * FROM tblTest

GROUP BY strLastName, strFirstName

See Also