Postgres do on update delete cascade on foreign key-CodePudding

I have two tables, domains and scopes. Scopes table references domains. Now, the issue is I want soft deletes on domains table (ie have a deleted column that I set to true to mark a domain as deleted - this is handled in application code already). But I want to have hard deletes in scopes table.

I know I can set the ON DELETE CASCADE in a foreign key, but that won't work here because I'm not actually deleting a row in the parent table. I also can't use ON UPDATE CASCADE because I don't want to update in the child table, I want to do a delete. So how do I accomplish this?

Here's the minimal schema:

create table domains
(
    domain_id                   varchar(100)            not null
        constraint domains_pkey
            primary key,
    deleted                     boolean   default false not null,
    constraint domains_deleted_constraint
        unique (domain_id, deleted)
);

create table scopes
(
    domain_id      varchar(100)            not null,
    scope_name     varchar(20)             not null,
    created_at     timestamp default now() not null,
    domain_deleted boolean   default false not null,
    description    varchar(500),
    constraint scopes_unique_constraint
        unique (domain_id, scope_name),
    constraint scopes_domain_id_fkey
        foreign key (domain_id, domain_deleted) references domains (domain_id, deleted)
            on update WHAT TO DO HERE?
);

I'm on Postgres 13.4

CodePudding user response：

The suggestion from Laurenz doesn't work as-is for my use case because I do want to keep the deleted in the foreign key - without it someone might insert a scope referencing a deleted domain, which I don't want.

However triggers seem to be the right approach. I added a trigger to delete the scope, and kept the foreign key as ON UPDATE CASCADE.

Now when deleted is set to true in a domain, cascade update makes domain_deleted to true in the corresponding rows in the scopes table, and the trigger just deletes the rows.

CREATE OR REPLACE FUNCTION delete_scope_on_soft_delete()
    RETURNS trigger AS
$func$
BEGIN
    DELETE FROM public.scopes WHERE scope_name = OLD.scope_name;
    RETURN NULL;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER scopes_update_deleted BEFORE UPDATE OF domain_deleted ON scopes 
FOR EACH ROW WHEN (new.domain_deleted = true) 
EXECUTE FUNCTION delete_scope_on_soft_delete();

CodePudding user response：

I'd say that you need a trigger:

CREATE FUNCTION del_scope() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF NEW.deleted = TRUE THEN
      DELETE FROM scopes
      WHERE domain_id = NEW.domain_id;
   END IF;

   RETURN NEW;
END;$$;

CREATE TRIGGER BEFORE DELETE ON domains
   FOR EACH ROW EXECUTE FUNCTION del_scope();

The foreign key should be changed to not include deleted.