April 21, 2008 at 3:27 pm
hi guys i am trying to do a simple update
Update dtblTelephones
set Code = 'CC'
where Code = 'BV'
Update dtblTechnicians
set Country = 'CC'
where Country = 'BV'
but i keep getting the error
The UPDATE statement conflicted with the REFERENCE constraint "FK_dtblTechnicians_tblTelephones". The conflict occurred in database "DBTechnicians", table "dbo.dtblTechnicians", column 'Country'.
I know it has to do with the foreign key constraint but dont' know how to solve it..
April 21, 2008 at 5:38 pm
Apparently dtblTelephones.Code is the same thing as dtblTechnicians.Country (not obvious by the names). So, all of the country values have to be in the telphones table.
If this is an exceptional, one-time update, disable the constraint, do the update, then put back the constraint. If this sort of thing is going to come up more, then you need cascading updates.
-- disable
ALTER TABLE [dtblTechnicians] NOCHECK CONSTRAINT [FK_dtblTechnicians_tblTelephones]
-- run the updates
Update dtblTelephones
set Code = 'CC'
where Code = 'BV'
Update dtblTechnicians
set Country = 'CC'
where Country = 'BV'
--re-enable
ALTER TABLE [dtblTechnicians] CHECK CONSTRAINT [FK_dtblTechnicians_tblTelephones]
April 21, 2008 at 8:40 pm
one time thing.. exactly what i needed!!! thank you so much :Wow:
April 22, 2008 at 1:40 pm
Depending on the FK constraint, doing them in reverse order may work also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply