Does not allow adding Primary key to a restored database.

Question

Our database crashed. In the time it took us to get the data from backup client had already entered new information into the database. So we basically have data in the database that is not in the backup. I do an import for the database(backup). It errored out that due to primary keys. I stripped the keys and it allowed me to import the old data in. but now when I go to add the primary keys again I get this error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DICOMImages' and the index name 'PK_DICOMImages'. The duplicate key value is (1.2.840.113543.6.6.4.0.6101748010736748252021090397294789472888).
Could not create constraint or index. See previous errors.
The statement has been terminated.

I have tried removing the keys from both Source and destination databases and still I get this error. I go to the "Design" screen and there are no keys mapped there.

I need to make sure that the Primary Keys are added to the tables.

Any help would be appreciated.

ajbehzadi

Answer 1

Say that you want to have a Primary Key on columns (a, b, c, d). You can identify duplicate rows this way:

WITH CTE AS (
   SELECT *, COUNT(*) OVER(PARTITION BY a, b, c, d) AS valuecnt
   FROM  tbl
)
SELECT * FROM CTE WHERE valuecnt > 1

It will be quite a mess to sort this out. It would probably have been better to keep the keys and had the two databases in parallel and try to run updates. But that would also be a lot of work depending on how much data the users have entered.

And by all means stay way from that Design screen! The Table Designer in SSMS is horribly buggy and can lead you into even deeper troubles.

Answer 2

How big is this table which is complaining about? Somehow the same entry exists and thus you cannot make a primary key without deleting this key first. There may be other duplicates already.