PostgreSQL双向引用场景下级联删除约束的问题排查与解决

我有variable表和variable_variable关联表，后者通过variable_id_from和variable_id_to记录变量间的引用关系——比如variable_id_from = 1, variable_id_to = 2就表示ID为1的变量引用ID为2的变量。需要实现以下业务规则：

• 要是变量A被其他变量引用（也就是variable_variable里存在variable_id_to = A.id的行），绝对不能删除A；
• 如果变量B没被其他变量引用，但B自己引用了别的变量，删除B时必须自动删掉variable_variable里所有variable_id_from = B.id的关联行；
• 支持自引用场景：如果变量有自引用的行（variable_id_from = X.id, variable_id_to = X.id），删除X时要能成功执行，同时把自引用行也删掉。

之前加的外键约束没达到预期效果：我先创建A和B，再建立A到B的引用，然后尝试删除B，结果B和关联行都被删了，但按规则这应该删除失败才对。

问题原因 #

你现在的第二个外键约束（variable_id_to关联variable(id)）没显式声明ON DELETE RESTRICT，虽然多数数据库默认是这个行为，但可能因为约束执行顺序或者数据库特性导致没生效；另外，这个约束的核心作用就是阻止删除被引用的变量，必须明确配置才能确保逻辑严谨。

正确的约束配置 #

直接用下面的SQL设置外键，就能严格符合业务需求：

ALTER TABLE variable_variable
    ADD CONSTRAINT variable_variable_from_fk
        FOREIGN KEY (variable_id_from) REFERENCES variable (id)
        ON DELETE CASCADE;

ALTER TABLE variable_variable
    ADD CONSTRAINT variable_variable_to_fk
        FOREIGN KEY (variable_id_to) REFERENCES variable (id)
        ON DELETE RESTRICT;

约束逻辑说明 #

1. variable_id_from的ON DELETE CASCADE：当某个变量被删除时，所有由它发起的引用（也就是它作为variable_id_from的行）会被自动级联删除，刚好满足“删除未被引用但有对外引用的变量时，清理它的关联引用记录”的需求；
2. variable_id_to的ON DELETE RESTRICT：只要variable_variable里存在以目标变量ID为variable_id_to的行（说明有其他变量引用它），尝试删除该变量时数据库会直接报错阻止操作，完美实现“禁止删除被引用变量”的规则；
3. 自引用场景处理：当变量有自引用行时，删除操作会先触发CASCADE删掉自引用行，这时候已经没有任何引用该变量的记录了，RESTRICT约束不会触发，变量就能正常被删除，符合要求。

测试验证 #

• 测试A引用B的场景：删除B时，数据库检测到variable_variable里有variable_id_to = B.id的行，触发RESTRICT约束，删除失败报错，和预期一致；
• 测试B引用C且无其他变量引用B的场景：删除B时，CASCADE自动删掉variable_variable里variable_id_from = B.id的行，随后B被成功删除；
• 测试自引用场景：删除自引用变量时，先级联删除自引用行，再删除变量，执行成功。

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