SQL Server update primary key that's also a foreign key in two tables

Question

I need to update the primary key for a record but it's also the foreign key in two other tables. And I need the updated primary key to be reflected in the child tables as well.

Here is my query and the error:

begin tran
update question set questionparent = 10000, questionid= 10005 where questionid = 11000;

Error  9/4/2009 10:04:49 AM 0:00:00.000 SQL Server Database Error: The UPDATE statement conflicted with the REFERENCE constraint "FK_GoalRequirement_Question". The conflict occurred in database "numgmttest", table "dbo.GoalRequirement", column 'QuestionID'. 14 0

I don't remember how to go about doing this so that's why I'm here. Any help?

Answer 1

Are your relationships using

ON UPDATE CASCADE

If they are then changing the key in the primary table will update the foreign keys.

e.g.

ALTER TABLE Books 
ADD CONSTRAINT fk_author 
FOREIGN KEY (AuthorID) 
REFERENCES Authors (AuthorID) ON UPDATE CASCADE

Answer 2

You may:

1. disable enforcing FK constraints temporarily (see here or here)
2. update your PK
3. update your FKs
4. enable back enforcing FK constraints

do it all within a transaction and make sure that if transaction fails, you roll it back properly and still enforce the FK constraints back.

But... why do you need to change a PK? I hope this is an action that is executed rarely (legacy data import or something like that).