40

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?

NMan
  • 611
  • 2
  • 8
  • 10
  • Another option: avoid having a changeable primary key. Use an IDENTITY as the primary key instead. Keep a unique constraint on the columns that need to be unique. That may be out of your control, but it works great for us. Our users can change the ID as much as they want, and it doesn't affect the actual primary key. – Paul Williams Sep 04 '09 at 14:23

6 Answers6

61

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
pjp
  • 17,039
  • 6
  • 33
  • 58
  • Anyone way to tell without looking at the script? I don't have the permissions to look at it. – NMan Sep 04 '09 at 14:18
  • 2
    sure: `BEGIN TRANSACTION; DELETE FROM ParentTable WHERE ID = ?; ROLLBACK;` if your see FK violation, then the update of FK is restricted. – van Sep 04 '09 at 14:29
  • 3
    This should be the accepted answer. I feel it most closely addresses the OP's question and does so in a better way than the currently-accepted answer. – rory.ap Dec 30 '13 at 18:35
  • @van, why would a DELETE have any bearing on an UPDATE CASCADE. Am I missing something? – Kirk Woll Jan 08 '14 at 16:10
  • @KirkWoll, my comment was on a smaller context, an answer for the comment above: how to check if FKs are enforced on the DB; it is out of the context of answering the question. So: you are right: one has nothing to do with the other. – van Jan 08 '14 at 19:17
  • @roryap: wow, this is an old question. I guess I agree with you on a straight-to-the-point basis if those PKs need to be updated from time to time. I did assume, however, that this is a rare bulk operation on large number of records. In this case I personally would still go for the accepted answer terms of performance... and disallow such changes in the usual business context. – van Jan 08 '14 at 19:22
27

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

Community
  • 1
  • 1
van
  • 74,297
  • 13
  • 168
  • 171
  • in my case because I have to merge data from a second DB instance so getting all the IDs above the range in the first DB will let me bulk insert from second DB into the first. – Ken Forslund Oct 23 '19 at 19:13
15

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)

rcp
  • 301
  • 4
  • 10
10

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.

Damien
  • 131
  • 1
  • 6
  • The advantage of this approach is it avoids adding cascades which can end up not being allowed in more complex situations. See: https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – shlgug Sep 17 '19 at 13:35
2

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
link2jagann
  • 183
  • 1
  • 5
  • 12
0
  • 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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Younes Meridji
  • 149
  • 5