Friday, February 18, 2011

Primary Key Constraint - duplicate values

Msg 1505, Level 16, State 1, Line 1The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.PTO_CHATTER' and the index name 'pk_chatter'. The duplicate key value is (100283).Msg 1750, Level 16, State 0, Line 1  Could not create constraint.   See previous errors.The statement has been terminated.

One of our developers was trying to make a certain column in a table a primary key and encountered the above error.  The column had duplicate values in it. A column which has duplicates can not be a primary key. Here is a query to find whether the column has duplicate values.

SELECT EMPLID,
COUNT(*)
FROM
dbo.PTO_CHATTER
GROUP BY EMPLID
HAVING
COUNT(*) > 1