How to do a cascading update?

Question

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

Is this possible?

Answer 1

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 breaks the general rules.

Answer 2

AFAIK, you can't add a CASCADE option to an UPDATE like you can with a DELETE.

Instead, do the following in a (probably in a transaction).

DROP original constraint / ADD temp constraint

Something like

ALTER fk_table DROP CONSTRAINT mytable__table_w_fk__fk;
ALTER fk_table ADD CONSTRAINT mytable__table_w_fk__fk
  FOREIGN KEY (fk) REFERENCES mytable (pk)
  ON UPDATE CASCADE;

Do the update

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

DROP temp constraint / ADD original constraint

ALTER fk_table DROP CONSTRAINT mytable__table_w_fk__fk;
ALTER fk_table ADD CONSTRAINT mytable__table_w_fk__fk
  FOREIGN KEY (fk) REFERENCES mytable (pk);