CODEWITHSUNDEEP
c#asp.netsql-server-ce
Leema
Leema

Reputation: 97

error while inserting primary key

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?

Upvotes: 0

Views: 513

Answers (2)

Per Hornshøj-Schierbeck
Per Hornshøj-Schierbeck

Reputation: 15196

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

Upvotes: 1

thomas
thomas

Reputation: 2642

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.

Upvotes: 0

Related Questions