如何进行级联更新? [英] How to do a cascading update?

问题描述

我有一组表，其中的列对另一个表中的用户名列具有外键约束.我被指示不要将 ON UPDATE CASCADE 添加到表定义中，因为我们不想悄悄地丢失诸如活动日志之类的信息.我们只想在这个实例中显式级联.不过，我还没有找到如何在查询中执行此操作的示例.我想它看起来像

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 CASCADE to 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

UPDATE CASCADE "MySchema"."MyTable"
SET user_name = "John Smith"
WHERE user_id = 1

这可能吗?

推荐答案

在 Postgres(和其他 RDBM)中，级联更新仅适用于外键.示例:

In Postgres (and other RDBMs) cascading updates apply exclusively to foreign keys. Example:

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.

这篇关于如何进行级联更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！