How to correct a primary key field in MS SQL 2014 I have a table that uses serial numbers as the primary key in a database. We had a bad import of some serial numbers, where a single apostrophe was included at the beginning of the field.

I attempted to remove it using the statement 

update [xxx].[dbo].[xxx] set [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','')


When I did, I got the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'xxx$PrimaryKey'. Cannot insert duplicate key in object 'dbo.xxx'. The duplicate key value is (123456789).


Is there a way that I can update this field and remove the single apostrophe without violating the constraint?
We would need the table definition for a definite answer. It looks like that when you remove the single apostrophe from the serial number, that that value is then the same as an already existing value. Hence the error message. 

It looks like the import file was corrected and imported again, resulting in multiple values containing, for example, values similar to 12345 and '12345.

Solution

I would try and search for the near identical primary keys first and see if you can delete them.

I created a dummy scenario with db&lt;>fiddle to show you how it would work.

Create Table

create table xxx (keynumber nvarchar(10) primary key clustered, comment nvarchar(20)) 


Insert Pseudo Data

insert into xxx (keynumber,comment) 
values 
('12345','First Comment'), 
('''12345','Second Comment'), 
('12346','Third Comment'), 
('''12346','Fourth Comment'),
('123457','Fith Comment')


SELECT Psuedo Duplicates

SELECT x1.keynumber, x2.keynumber 
FROM [xxx].[dbo].[xxx] as x1 
     JOIN [xxx].[dbo].[xxx] as x2
     ON '''' + x1.keynumber = x2.keynumber


Results



keynumber | keynumber
:-------- | :--------
12345     | '12345   
12346     | '12346   



You can then either deleted the duplicates or rename them (see Alternative)

Once you have cleaned up the duplicates, you will be able to run your UPDATE.... Before that you have some manual labour to do.

Alternative / Renaming

Instead of just removing the apostrophe, add an additional value to the keynumber values that have an apostrophe. E.g. add CHK to the keynumber

UPDATE [xxx].[dbo].[xxx] 
SET [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','CHK')


This way you will be able to see which values have to be checked.

Observations

If the column keynumber would have been an int, then it would have been impossible to enter '12345 into the table.