SQL Server 更新主键，同时也是两个表中的外键 [英] 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.

这是我的查询和错误:

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?

推荐答案

您可以:

1. 暂时禁用强制 FK 约束(请参阅此处或此处)
2. 更新您的 PK
3. 更新您的 FK
4. 启用反向执行 FK 约束

在一个事务中完成所有操作，并确保如果事务失败，您可以正确回滚它并仍然强制执行 FK 约束.

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.

但是……为什么要换PK?我希望这是一个很少执行的操作(旧数据导入或类似的操作).

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

这篇关于SQL Server 更新主键，同时也是两个表中的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！