更新外键值

发布于 2024-08-09 15:29:14 字数 902 浏览 5 评论 0原文

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

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表，所以仅仅删除它不是一个选择。

我的大问题是，如果我想更新整个组的group_owner_id，我正在编写这样的代码（以伪代码）：

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

有没有办法解决这个问题？看起来有点笨拙。希望我已经发布了足够的细节。

谢谢。

原文

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

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.

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.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

仅此而已 2024-08-16 15:29:14

SQL Server 不支持 UPDATE CASCADE 吗？ :-

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

然后您只需更新 Group 表的 group_owner_id 即可。

Does SQL Server not support UPDATE CASCADE? :-

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

Then you simply update the Group table's group_owner_id.

回复收藏 0 原文

夢归不見 2024-08-16 15:29:14

托尼·安德鲁的建议有效。例如，假设您想要将组 1 的所有者从 2 更改为 5。启用 ON UPDATE CASCADE 时，此查询：

update [Group] set group_owner_id = 5 where group_id = 1

将自动更新 GroupItem 中的所有行。

如果您不控制数据库中的索引和键，可以通过首先插入新组，然后修改依赖表中的所有行，最后删除原始组来解决此问题：

insert into [Group] values (1,5)
update [GroupItem] set group_owner_id = 5 where group_id = 1
delete from [Group] where group_id = 1 and group_owner_id = 2

顺便说一下，GROUP 是一个 SQL关键字且不能是表名。但我假设你的真实表格有真实姓名，所以这不是问题。

Tony Andrew's suggestion works. For example, say you'd like to change the owner of group 1 from 2 to 5. When ON UPDATE CASCADE is enabled, this query:

update [Group] set group_owner_id = 5 where group_id = 1

will automatically update all rows in GroupItem.

If you don't control the indexes and keys in the database, you can work around this by first inserting the new group, then modifying all rows in the dependant table, and lastly deleting the original group:

insert into [Group] values (1,5)
update [GroupItem] set group_owner_id = 5 where group_id = 1
delete from [Group] where group_id = 1 and group_owner_id = 2

By the way, GROUP is a SQL keyword and cannot be a table name. But I assume your real tables have real names so this is not an issue.

回复收藏 0 原文