更新外键值 [英] Updating foreign key values

问题描述

我有一个数据库应用程序，其中的组建模如下:

I have a database application in which a group is modeled like this:

TABLE Group
(
  group_id integer primary key,
  group_owner_id integer
)

TABLE GroupItem
(
  item_id integer primary key,
  group_id integer,
  group_owner_id integer,
  Foreign Key (group_id, group_owner_id) references Group(group_id, group_owner_id)
)

我们建立了一个包含group_owner_id的多字段外键，因为我们要确保GroupItem不能拥有与其所在的Group不同的所有者.出于其他原因(我不认为我需要对此进行详细说明)无法从GroupItem表中删除group_owner_id，因此仅删除它是不可行的.

We have a multi field foreign key set up including the group_owner_id because we want to ensure that a GroupItem cannot have a different owner than the Group it is in. For other reasons (I don't think I need to go into detail on this) the group_owner_id cannot be removed from the GroupItem table, so just removing it is not an option.

我的最大问题是，如果我想为整个组更新group_owner_id，我正在写这样的代码(用伪代码):

My big problem is if i want to update the group_owner_id for the entire group, I'm writing code like this (in pseudo code):

...
BeginTransaction();
BreakForeignKeys(group_items);
SetOwnerId(group, new_owner_id);
SaveGroup(group);
SetOwnerId(group_items, new_owner_id);
SetForeignKeys(group_items, group);
SaveGroupItems(group_items);
CommitTransaction()
...

有没有办法做到这一点?似乎有点笨拙.希望我已经发布了足够的细节.

Is there a way around doing this? It seems a bit clunky. Hopefully, I've posted enough detail.

谢谢.

推荐答案

SQL Server是否不支持UPDATE CASCADE? :-

Does SQL Server not support UPDATE CASCADE? :-

Foreign Key (group_id, group_owner_id)
 references Group(group_id, group_owner_id)
 ON UPDATE CASCADE

然后，您只需更新Group表的group_owner_id.

Then you simply update the Group table's group_owner_id.

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