更新外键值 [英] 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屋!