29

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?

Joe M
  • 3,060
  • 3
  • 40
  • 63
  • You do it in your data definition, `CREATE TABLE`, of your referencing row not in an updating query, as very well demonstrated in the accepted answer. – sçuçu Oct 10 '18 at 13:34

2 Answers2

42

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.

Mihai Chelaru
  • 7,614
  • 14
  • 45
  • 51
klin
  • 112,967
  • 15
  • 204
  • 232
  • 2
    Does it contain false or misleading information? Deserves downvoting? – klin Jun 29 '16 at 18:34
  • 5
    So I'd like the person who downvoted to show me the right direction. – klin Jun 29 '16 at 19:56
  • 2
    If you want to reference different column, use `group_id int references groups(different_column) on update cascade` – Tony Aug 02 '19 at 08:03
  • One other thing to note for @Tony's comment is that `different_column` must at least be `UNIQUE` in order to use it as a reference point of the constraint – Torc Sep 23 '20 at 00:09
  • 1
    The answer could be improved by either showing how to do what the question asks for or explaining why it's not possible and what other options there are. – CervEd May 18 '21 at 20:29
  • 1
    I upvoted, but why is this feature available only for foreign keys? Wouldn't it make more sense to apply a setting once to a primary key column and be assured that updates would cascade to all the corresponding foreign keys? That's how I assumed it would work initially. – Harsha Laxman May 04 '22 at 18:32
  • 2
    @HarshaLaxman - The rule in force gives more flexibility in designing relations. A table may contain many foreign keys, some of them should be updated automatically, others not. And conversely, a target table can be referenced by one table with cascaded updates and by another without them, depending on the designer's decision. – klin May 04 '22 at 21:30
6

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);
CervEd
  • 3,306
  • 28
  • 25