CODEWITHSUNDEEP
sql-server
4thSpace
4thSpace

Reputation: 44312

How to update column with unique key constraint?

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?

Upvotes: 4

Views: 8616

Answers (1)

M.Ali
M.Ali

Reputation: 69494

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

Upvotes: 2

Related Questions