updating a column which thrown error

Hello All i am updating a column then it has thrown the following error please find the update query and the error:

update table_jboss_tomcat set employee_id = 433 where employee_id in (453,467)

Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'IX_Table_jboss_tomcat'. Cannot insert duplicate key in object 'dbo.table_jboss_tomcat'.

as i am updating with new employeeid then it should be updated but its not happening please help in this regards.

more ▼

asked Jun 04, 2012 at 01:32 PM in Default

avatar image

921 40 50 56

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

hello All i deleted a row which was already present with the same employee id and all other columns just like other employee id when i deleted that perticular row then the update ran successfully thanks for all ur support.

more ▼

answered Jun 05, 2012 at 12:28 PM

avatar image

921 40 50 56

(comments are locked)
10|1200 characters needed characters left

It looks like you have a unique constraint, IX_Table_jboss,tomcat, probably on the employee_id column, that is preventing you from adding another employee with an id of 433. It's pretty straight-forward. If you have a unique constraint, it prevents duplicates from being added. Also, your IN clause has two values. If you have matches for both, then you're trying to violate the unique constraint again. You can only have a single value equal to 433 in the table.

more ▼

answered Jun 04, 2012 at 01:39 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

even i tried to update like update table_jboss_tomcat set employee_id = 433 where employee_id = 453 then also it has thrown the same error please suggest the resolution for this how can i update i am not adding duplicates but i am replacing the employeeid 453 with 433

Jun 04, 2012 at 01:41 PM sand143eep
  • Excellent catch on the IN clause. I missed that one. ;-)

Jun 04, 2012 at 01:46 PM JohnM
(comments are locked)
10|1200 characters needed characters left

Assuming that there aren't any triggers on the table, the error is telling you that there is currently a unique key constraint on the employee_ID column and that there is already a row in the table that has the value of '433'. Of course, I can't see the constraint DML, so you would need to confirm that's what the constraint is indeed enforcing.

Can you confirm that a row with that value already exists?

 SELECT employee_ID from table_jboss_tomcat where employee_ID = 433

If so, you'll need to either remove/adjust the unique constraint or change the update statement to set the value of employee_ID to something other than 433 and to a value that doesn't already exist in the table. The constraint is doing it's job (correctly I might add) by enforcing the referential integrity of the table.

Hope this helps!

more ▼

answered Jun 04, 2012 at 01:45 PM

avatar image

14.4k 3 7 15

As @Grant Fritchey wisely pointed out, you'll also have to adjust your IN clause as it'll violate the unique constraint every time.

Jun 04, 2012 at 01:48 PM JohnM

yes there is already 300 rows with employeeid 433 and 256 rows with empid 453 now i need to replace 453 with 433 as 453 is expired and the 433 has look after the roles please suggest how to proceed there was a foreign key present.on that column

Jun 04, 2012 at 01:54 PM sand143eep

That doesn't seem possible unless the constraint was added after the fact with 'NOCHECK'. In this case, you would need to drop the constraint, alter the data, and then re-create the constraint with the same 'NOCHECK'. If this is the case, then I'd have to ask why have the constraint there in the first place?

NOTE: this is not the preferred manner to manage the data. I assume that the constraint was placed there for a reason. Playing with the integrity of the table in this manner can & will lead to a number of issues at some point. Also, if you are going to modify/drop the constraint, I would strongly advise you to test that first to ensure that it doesn't cause other issues while you update the rows.

Just my opinion on it.

Hope this helps.

Jun 04, 2012 at 02:09 PM JohnM

I will also point out that BOL states that using 'WITH NOCHECK' is something that they "do not recommend doing this, except in rare cases".


Just passing it along.

Jun 04, 2012 at 02:13 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 04, 2012 at 01:32 PM

Seen: 918 times

Last Updated: Jun 05, 2012 at 12:28 PM

Copyright 2018 Redgate Software. Privacy Policy