How to update column with unique key constraint?

Question

I've inserted several rows into a table, using duplicates in a column (theId) with a unique key constraint. I did this by setting IDENTITY_INSERT to off then on.

I tried this same technique to update, since I need to change those dupe values but it isn't working:

SET IDENTITY_INSERT mytable OFF
update mytable set
theId = 5
WHERE mytableId in (40, 41)
SET IDENTITY_INSERT mytable ON

Error: Violation of UNIQUE KEY constraint 'XI_mytale_mytableId_othercolumn_U'. Cannot insert duplicate key in object 'dbo.mytable'.

Any ideas how this can be done with an UPDATE?

Please explain what you did more thorougly. First two sentences are describing something impossible. — OzrenTkalcecKrznaric
SET IDENTITY_INSERT mytable ON; will allow you to manually add values in an Identity Column i.e Auto-increment column. It has nothing to do with Unique Constraint — M.Ali

M.Ali M.Ali · Accepted Answer · 2013-10-25T19:05:13

I think you are confusing with An Identity Column where you managed to add values manually after Setting SET IDENTITY_INSERT mytable ON; with a Column with Unique Constraint Defined on it. A column with Unique Constraint will never allow you to add duplicate values.
If you do want to be able to add Duplicate values in a column with a unique constraint you can simple drop the unique constraint as why have a unique constraint when you dont have unique values.
You can droo the Unique Constraint using following statement,

ALTER TABLE TableName
DROP CONSTRAINT uc_ConstraintName

How to update column with unique key constraint?

1 Answers