Postgres父表软删时自动硬删关联子表数据的解决方案咨询

你当前的场景无法通过外键的级联规则实现，因为外键ON UPDATE仅支持同步更新子表关联字段，无法直接触发子表行删除，最稳妥的实现方式是使用PostgreSQL触发器，具体操作如下：

1. 编写触发器函数 #

该函数会判断domains表的deleted字段是否从false改为true，如果是则删除scopes表中关联的所有行：

CREATE OR REPLACE FUNCTION delete_related_scopes_on_domain_soft_delete()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.deleted = FALSE AND NEW.deleted = TRUE THEN
    DELETE FROM scopes WHERE domain_id = NEW.domain_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到domains表 #

触发器仅在deleted字段被更新时触发，避免无效执行：

CREATE TRIGGER trg_after_domain_soft_delete
AFTER UPDATE OF deleted ON domains
FOR EACH ROW
EXECUTE FUNCTION delete_related_scopes_on_domain_soft_delete();

额外说明 #

• 你现有表结构中scopes表的domain_deleted字段、以及(domain_id, domain_deleted)的联合外键可以直接移除，冗余字段不需要额外维护，简化后的scopes表结构如下：

create table scopes
(
    domain_id      varchar(100)            not null,
    scope_name     varchar(20)             not null,
    created_at     timestamp default now() not null,
    description    varchar(500),
    constraint scopes_unique_constraint
        unique (domain_id, scope_name),
    constraint scopes_domain_id_fkey
        foreign key (domain_id) references domains (domain_id)
);

• 如果你不想使用数据库触发器，也可以在应用层的软删除事务中，新增删除对应scopes行的逻辑，需要保证两个操作在同一个事务内执行，保证原子性。
• 该方案完全兼容PostgreSQL 13.4版本，无兼容问题。

内容的提问来源于stack exchange，提问作者hoodakaushal