error while inserting primary key

Question

I'm trying (SQL Server Compact) to add primary key constraint on existing table that has some rows in it. While adding primary key I'm getting the error:

"A duplicate key cannot be inserted into a unique index"

I don't what this is, can anyone help me with this?

Answer 1

Make sure the data in the table respects the contraint you're trying to set on the table. If the column you are making primary has duplicate entries, it won't be able to work as primary key, hence the error.

You could try and find the rows with duplicate entries, with something like this:

select Id, Count(*) from myTable
having Count(*) > 1
group by Id

Answer 2

Try this

select id_column, count(*) from your_table group by id_column having count(*) > 1

If there are any records returned from this above query you cannot add a primary key on id_column since duplicate IDs exist.

Of course you will need to replace id_column and your_table with the appropriate names.