I’ve constructed a table in Postgres (v14.4) like this:
CREATE TABLE public.comments
(
comment_id uuid NOT NULL UNIQUE DEFAULT uuid_generate_v4(),
media_id uuid NOT NULL,
user_id uuid NOT NULL,
parent_id uuid NULL,
comment text NOT NULL,
date_created timestamptz NOT NULL DEFAULT now(),
date_updated timestamptz NOT NULL DEFAULT now(),
date_deleted timestamptz,
CONSTRAINT fk_comments_parent
FOREIGN KEY(parent_id)
REFERENCES comments(comment_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
I added some sample data to the table:
I then ran this SQL command:
DELETE FROM comments
WHERE comment_id = '198332bc-ee11-421b-b33f-3a711a94c0b0'
As expected, this deleted the last record shown in the screenshot and the 2nd record shown, which is connected via the parent_id. Great.
I restored all of the data and then tried this
UPDATE comments
SET date_deleted = '2024-09-08 10:30:00.000 -0400'
WHERE comment_id = '198332bc-ee11-421b-b33f-3a711a94c0b0'
I was expecting date_deleted to be thusly set in both records (parent and child) but it was only set in the parent record.
Simply put, how come the UPDATE CASCADE didn’t update date_deleted for the 2nd record shown in the screenshot?
Am I doing something wrong or is what I’m asking Postgres to do currently impossible?
2
You are misunderstanding what ON UPDATE CASCADE does. What is does is the following: if you update comment_id in a row in comments to a new value X, the column parent_id in all rows that reference that row is also updated to X. In short, the referencing rows are automatically updated so that they still reference the modified row.
The foreign key does not establish a reference between any other columns in the affected rows, so such columns are not updated.
What you want can best be achieved with a row-level trigger.
3