x

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 '12 at 01:32 PM in Default

sand143eep gravatar image

sand143eep
771 20 33 39

(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 '12 at 12:28 PM

sand143eep gravatar image

sand143eep
771 20 33 39

(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 '12 at 01:39 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

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 '12 at 01:41 PM sand143eep
+1 Excellent catch on the IN clause. I missed that one. ;-)
Jun 04 '12 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 '12 at 01:45 PM

JohnM gravatar image

JohnM
6k 1 3 7

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 '12 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 '12 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 '12 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".

http://msdn.microsoft.com/en-us/library/ms190273(v=sql.90).aspx

Just passing it along.
Jun 04 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933

asked: Jun 04 '12 at 01:32 PM

Seen: 632 times

Last Updated: Jun 05 '12 at 12:28 PM