SQL:在 OneToOne 关系中删除子项(但理论上是父项)时删除父项

Question

亲爱的 Stack Overflow 社区，您好，

我已经搜索了很多小时，但我找不到解决方案，这是我非常独特的情况。
也许你们可以提供帮助。

假设我们有 two tables .

|domain_realm                     |   | password_policy                        |
|-------------------------------- |   | ---------------------------------------|
|id    domain  password_policy_id |   | id  min_chars  min_numbers  min_length |
|1     1       11                 |   | 11  3          2                       |
|2     1       12                 |   | 12  4          1                       |

一个叫做domain_realm，另一个叫做password_policy。

这是一对一的关系，因此一个domain_realm只有一个password_policy。

现在出现了这种情况下非常独特的情况:
domain_realm 持有 password_policy_id(或多或少 fk)，而不是 password_policy 持有其父级的外键。

Why would someone do this?
Let's assume password_policy is part of a package for many other applications not working with domain realms. Therefore keeping the table clean of any specific rows no other application needs (e.g. fk_domain_relam) is mandatory.

---

现在似乎不可能在删除 domain_realm 时自动删除(ON DELETE CASCADE)相关的 password_policy。

有没有办法以 SQL 方式执行此操作，还是我必须知道并确保在我的代码中删除 password_policy？

那会很讨厌，因为如果我必须直接在数据库中删除 domain_realm(调试或支持原因)，我已经创建了一个孤儿，我必须知道这一点。否则孤儿将永远留在那个数据库中。

非常感谢您阅读本文。

Answer 1

您可以在从 domain_realm 中删除后创建触发器。我不是 100% 确定 postgresql 中的语法，但它应该看起来像这样:

CREATE OR REPLACE FUNCTION update_policy() RETURNS TRIGGER AS $$
    BEGIN
            DELETE FROM password_policy WHERE password_policy_id = OLD.password_policy_id;
            IF NOT FOUND THEN RETURN NULL; END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER clean_policy
AFTER DELETE ON domain_realm
FOR EACH ROW EXECUTE PROCEDURE update_policy();