更改主键值 [英] change primary key value

问题描述

我有 10 张桌子.每个表被其他5个表的外键引用.

I have 10 tables. Each table referenced by foreign keys of other 5 tables.

我需要更改这 10 个表的主键值.有什么办法可以改变它，让它自动改变所有的外键?

I need to change the primary key value of those 10 tables. Is there any way to change it so that it will change automatically all the foreign keys?

我正在使用 sql server 2008 并拥有管理工作室.

I am using sql server 2008 and have the management studio.

推荐答案

你需要为那些外键设置ON UPDATE CASCADE:

You need to set ON UPDATE CASCADE for those foreign keys:

ALTER TABLE bar
ADD CONSTRAINT FK_foo_bar
FOREIGN KEY (fooid) REFERENCES foo(id)
ON UPDATE CASCADE

然后您只需更新 FK，引用字段也将作为交易的一部分进行更新:

Then you simply update the FKs and referring fields will also be updated as part of the transaction:

UPDATE foo SET id = id + 1000

请注意，要更改约束，需要删除它们.

Note that to alter constraints they need to be dropped.

这篇关于更改主键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！