CODEWITHSUNDEEP
sql-server
NMan
NMan

Reputation: 641

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

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?

Upvotes: 43

Views: 96766

Answers (6)

link2jagann
link2jagann

Reputation: 193

Go to foreign Key Relations of each child tables and on Insert and Update specification change delete and update rules to cascade.

Upvotes: 2

Younes Meridji
Younes Meridji

Reputation: 309

  • create a New row with the same data and a different primary key.
  • update all the children tables.
  • remove the row that you repeated its data

And its done.

Upvotes: 1

van
van

Reputation: 76962

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).

Upvotes: 29

Damien
Damien

Reputation: 171

As I'm not too confident disabling FK constraints, I prefer too :

  1. Duplicate the row with the old PK with one with the new PK
  2. Update the FKs
  3. Delete the row with the old PK

Advantage : No constraint violated during the process.

Upvotes: 14

rcp
rcp

Reputation: 311

If you would like to set the Cascade rule graphically then Set Cascade Rule on SQL Management Studio

  1. Open table in design mode
  2. Click Relationship button from top toolbar
  3. Select the required FK relations (one by one)
  4. Right Side - Expand INSERT or UPDATE Specification
  5. Change the UPDATE Rule to - Cascade

Close and Save, Done!

(Tried on SQL 2008)

Upvotes: 15

pjp
pjp

Reputation: 17629

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

Upvotes: 61

Related Questions