Unique Constraint Violation

Question

Unique Constraint Violation

mashikoo

Ten Centuries

Points: 1188
More actions
April 30, 2010 at 7:34 am

#221965

I'm working on updating a student's training history in my SQL Server database. However I get a 'primary key' violation when I try to update the employee number.
Do you know a way to update this value without disabling the constraint? Any suggestion will be appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Sounds to me like you are trying to change the Employee Number to a value that already exists in the database. Try running a SELECT statement on the table for that Employee Number.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 2

What was the error message? If it was actually a foreign key violation then enable the ON UPDATE CASCADE option on the foreign key constraint.

If it really was a unique constraint violation then you need to avoid doing an UPDATE that violates the constraint. Assuming you are trying to change more than one row then try to make it into one single statement so that the constraint won't be violated. MERGE with a join could help you.

mashikoo Ten Centuries Points: 1188 More actions · Answer 3

Thanks David and Lynn..That's the thing I'm changing more than one column in that row. I may just insert a new record into the table instead of jumping through these hoops. I really don't want to alter the constraint. That's not something I want to do in a production database just to modify one record. Any more ideas will be appreciated!

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 4

If you are only changing one row then I don't understand your problem. Either the constraint is violated or it isn't.

A single UPDATE statement can change multiple columns. What is the actual code you are running?

mashikoo Ten Centuries Points: 1188 More actions · Answer 5

Thanks again David..I lost the queries I wrote when my machine rebooted this morning. It would take me a little while to rewrite them and I'm entagled in other things right now.

Basically what I'm doing is modifying an existing employee number in the course histories table with the employee number of the person who actually took the class. I know this value is referenced in other tables and I was going to change it in each place with the identifying value being the course id. Your suggestion will be appreciated.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 6

From your description it sounds like your problem may be a foreign key violation. Is that so? If so then you could use the ON UPDATE CASCADE option to ensure the referencing rows get updated.

The only reason you would get a unique constraint violation from updating a row would be if the new value already existed in the table. In which case, don't update it, because it already exists.

mashikoo Ten Centuries Points: 1188 More actions · Answer 7

I really do appreciate your help David... It's a primary key violation. Once i rewrite the query, if it bombs out again, I'll send you the error message.