选择 PostgreSQL 外键删除策略而不丢失错误的行

追踪一个隔离的示例

这个说明性脚本使用三个不同的子表和三个不同的父行，以保持策略相互独立。它不会将三个相互冲突的策略附加到一个关系上。仅在一次性会话中运行此类演示；事务以 ROLLBACK 结束，并不声称要测试你的生产架构。

理解预期结果和被拒绝的情况

预期的级联计数为零，因为链接到父记录 2 的子记录已被删除。id 为 30 的行保留在 demo_null 中，且 parent_id 等于 null。脚本不会删除父记录 1，因此其 restrict 子记录仍然存在。这些结果来自所述的约束和示例数据；它们是说明性的，而非度量结果。

在回滚之前尝试删除父记录 1 将被拒绝，因为 demo_restrict 仍然引用它。该故意失败的语句被从脚本中省略：错误可能会中止事务，后续 SQL 需要适当的回滚处理。

检查真实删除前的范围

统计与提议的父记录谓词匹配的行，并检查依赖表。级联可以继续通过进一步的关系，并删除比单个子表计数所暗示的更多行。审查完整的依赖链以及应用程序后果。

预览查询和随后的 DELETE 并不是在并发更改下自动成为受保护的原子决策。为真实操作规划所需的事务和锁定行为，而不是假设早期计数会冻结数据。

检查性能，但不声称有保证的速度

删除被引用的行可能需要在引用侧查找行。PostgreSQL 并不会仅仅因为你声明了外键就自动在引用外键列上创建索引。在决定是否需要另一个索引之前，检查现有索引和工作负载。

大型级联可能会持有锁，产生大量数据库工作，并影响并发用户。使用适当的维护过程和恢复计划。小型临时表示例仅确立行为，而非生产删除的成本。

BEGIN;
CREATE TEMP TABLE demo_parent (id integer PRIMARY KEY);
CREATE TEMP TABLE demo_restrict (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE RESTRICT
);
CREATE TEMP TABLE demo_cascade (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE CASCADE
);
CREATE TEMP TABLE demo_null (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE SET NULL
);
INSERT INTO demo_parent VALUES (1), (2), (3);
INSERT INTO demo_restrict VALUES (10, 1);
INSERT INTO demo_cascade VALUES (20, 2);
INSERT INTO demo_null VALUES (30, 3);
DELETE FROM demo_parent WHERE id IN (2, 3);
SELECT count(*) AS remaining_cascade_rows FROM demo_cascade;
SELECT id, parent_id FROM demo_null ORDER BY id;
ROLLBACK;

将关系操作与应用程序清理分开

数据库约束作用于声明的数据库关系上。它们不能保证清理事务之外维护的文件、远程服务或事件。必须明确考虑这些副作用。

回滚在示例中保护事务性数据库更改，而非外部系统执行的任意操作。如果部署的策略错误，将其更改视为经过审查的架构变更，而不是对一次失败请求的快速变通。

决定子记录是否可以独立存在

没有父记录就没有意义的明细行可能适合级联删除。必须保留的历史记录可能需要限制删除或保留带有可选关系的行。从这个业务决策入手，而不是选择让失败的 DELETE 成功的动作。

外键保护其约束中声明的关系。它本身并不强制执行保留策略、获取删除个人数据的权限，或保留与记录关联的外部文件。

检查实际应用的约束

在数据库架构或管理工具中查找被引用的表、引用的列以及配置的 ON DELETE 动作。不要根据列的名称或可能与已部署数据库不匹配的 ORM 模型来推断该动作。

CASCADE 和 SET NULL 属于外键定义中 REFERENCES 子句之后的部分。诸如 DELETE FROM parent WHERE id = 1 CASCADE 这样的命令不是用于选择外键动作的 PostgreSQL DELETE 语法。

比较这三种动作

使用 RESTRICT 时，尝试删除仍被子记录引用的父记录会被拒绝。使用 CASCADE 时，父记录的删除会移除引用它的子记录。使用 SET NULL 时，子记录保留但其引用列变为 null。其他约束仍然适用，可能会阻止操作。

SET NULL 仅在受影响的列和应用程序逻辑接受 null 时才适用。NOT NULL 约束可能导致删除失败。NO ACTION 是另一种策略，但不应在所有情况下将其描述为与 RESTRICT 相同：延迟约束检查可能使这种区别变得重要。