postgresql 如何进行级联更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38103781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do a cascading update?
提问by Joe M
I have a group of tables with columns that have foreign key constraints on a user name column in another table. I've been instructed not to add ON UPDATE CASCADEto the table definition because we don't want to silently lose information from things like activity logs. We only want to explicitly cascade in this one instance. I haven't been able to find an example of how to do it in a query though. I'm thinking it would look something like
我有一组表，其中的列对另一个表中的用户名列具有外键约束。我已被指示不要添加ON UPDATE CASCADE到表定义中，因为我们不想悄悄地丢失诸如活动日志之类的信息。我们只想在这个实例中显式级联。不过，我还没有找到如何在查询中执行此操作的示例。我想它看起来像
UPDATE CASCADE "MySchema"."MyTable"
SET user_name = "John Smith"
WHERE user_id = 1
Is this possible?
这可能吗？
回答by klin
In Postgres (and other RDBMs) cascading updates apply exclusively to foreign keys. Example:
在 Postgres（和其他 RDBM）中，级联更新仅适用于外键。例子：
create table groups (
group_id int primary key
);
create table users (
user_id int primary key,
group_id int references groups on update cascade
);
insert into groups values (1);
insert into users values (1, 1);
update groups set group_id = 10 where group_id = 1;
select * from users;
user_id | group_id
---------+----------
1 | 10
(1 row)
In fact, other options are not needed. If you feel the need to do this for a column which is not a foreign key, it means that the model is poorly designed (it is not normalized). On the other hand, the possibility of selective cascaded update of foreign keys does not solve any practical problem but rather brakes the general rules.
事实上，不需要其他选项。如果您觉得需要对不是外键的列执行此操作，则意味着模型设计不佳（未规范化）。另一方面，选择性级联更新外键的可能性并没有解决任何实际问题，而是阻碍了一般规则。
