PostgreSQL：选择ON DELETE RESTRICT、CASCADE或SET NULL而不丢失相关记录

将删除策略视为数据保留决策

每个外键都隐含地回答了一个业务问题：当被引用的行消失时，指向它的行应该发生什么？ON DELETE RESTRICT的回答是依赖项必须存活，并且在这些依赖项被显式处理之前，父项不能被移除。ON DELETE CASCADE的回答是依赖项是不可分割的父项部分，应该一起消失。ON DELETE SET NULL的回答是引用是可选的，依赖行可以在没有引用的情况下继续存在。这些不是可互换的语法选项；它们编码了影响删除后可查询数据的保留规则。

PostgreSQL文档指出，这些动作反映了直观的选择：禁止删除被引用的产品，同时删除订单，或者其他情况。选错可能会静默地移除另一个关系需要保留的行，或者因为依赖项从未打算存活而阻止合法的清理。

这个决策应该遵循关系的含义，而不是模式作者的便利性。被历史订单引用的产品目录条目与仅因订单存在而存在的订单项行是不同的。

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);
CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text);
CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

在更改之前阅读默认的NO ACTION行为

如果你编写的外键没有ON DELETE子句，PostgreSQL会应用ON DELETE NO ACTION。文档解释说，这意味着允许被引用表中的删除进行，但仍然要求满足外键约束，因此该操作通常会导致错误。与RESTRICT的关键区别在于时机和可延迟性。如果约束是可延迟的，NO ACTION在语句结束时或事务结束时检查约束，给其他命令在检查触发之前修复情况的机会。RESTRICT立即检查且不能延迟。

依赖隐式默认值很脆弱，因为模式的读者无法判断作者是有意制定策略还是仅仅忘记指定。使动作显式化，即使是NO ACTION，也能记录决策并消除代码审查或迁移期间的歧义。

-- These two are NOT equivalent:
-- product_no integer REFERENCES products  -- defaults to NO ACTION, deferrable
-- product_no integer REFERENCES products ON DELETE RESTRICT  -- immediate, non-deferrable

当被引用行必须存活时选择RESTRICT

当在存在依赖行时删除父行应该直接失败时，使用ON DELETE RESTRICT。这迫使应用程序或操作员在移除父项之前对依赖项做出显式决定。PostgreSQL文档用产品和订单项的例子说明了这一点：产品和订单是不同的东西，因此让删除产品自动导致删除某些订单项可能被认为是成问题的。

RESTRICT是主数据、参考表以及任何其移除具有需要人工审查的下游后果的实体的正确默认值。来自RESTRICT的错误消息是确定性的且立即发生的，使其易于在应用程序代码中捕获并向用户呈现有意义的消息。

-- Attempting this with RESTRICT on product_no will raise an error:
DELETE FROM products WHERE product_no = 7;
-- ERROR: update or delete on table "products" violates
--        foreign key constraint on table "order_items"

仅为依赖生命周期行选择CASCADE

当子行仅作为父行生命周期的一部分存在且没有独立意义时，ON DELETE CASCADE是合适的。文档指出，订单项是订单的一部分，如果它们在订单被删除时自动删除会很方便。级联从一个操作中被引用行传播到每一个引用行。

危险在于CASCADE可能会移除另一个关系需要保留的数据。如果一个order_item行也被运输清单或审计日志引用，从orders级联删除将移除order_item，然后沿着那些其他外键违反或进一步级联。只有当你追踪了每一个下游引用并确认自动移除是所有这些引用的预期行为时，才使用CASCADE。

-- Deleting an order cascades to its line items:
DELETE FROM orders WHERE order_id = 42;
-- All rows in order_items with order_id = 42 are removed automatically.

仅为可选引用选择SET NULL

ON DELETE SET NULL保留引用行但将外键列设置为NULL。当关系代表可选信息时，这是合适的。文档给出了产品经理引用的例子：如果产品经理条目被删除，将产品的产品经理设置为null可能是有用的。

SET NULL要求引用列接受NULL值。它不能用于NOT NULL列或作为主键一部分的列，除非经过仔细规划。对于复合外键，列列表形式允许你只将引用列的子集设置为NULL，而其他列保持不变，这在复合键的一部分必须保持填充时是必要的。

-- Column-list form for composite foreign keys:
CREATE TABLE posts (
  tenant_id integer REFERENCES tenants ON DELETE CASCADE,
  post_id integer NOT NULL,
  author_id integer,
  PRIMARY KEY (tenant_id, post_id),
  FOREIGN KEY (tenant_id, author_id) REFERENCES users
    ON DELETE SET NULL (author_id)
);
-- Without the column list, tenant_id would also be set to NULL,
-- violating the primary key.

在一个小模式上比较三种动作

考虑文档中的products、orders和order_items模式。order_items表有两个具有不同动作的外键：product_no使用RESTRICT，order_id使用CASCADE。如果你尝试执行DELETE FROM products WHERE product_no = 7并且该产品出现在任何order_items行中，语句将立即因外键违反错误而失败。两个表中都没有行被移除。

如果你尝试执行DELETE FROM orders WHERE order_id = 42，CASCADE动作将移除引用该订单的每一个order_items行，并且删除成功。相同的模式展示了取决于你针对哪个父项的保护性和自动行为。

例如，如果orders有一个可选的assigned_shipper列引用shipper表，则SET NULL将适用。删除shipper将留下订单完好无损，assigned_shipper设置为NULL，在移除现在无效的引用的同时保留订单历史。

-- Outcome table for the example schema:
-- DELETE FROM products WHERE product_no = 7
--   -> ERROR (RESTRICT blocks it, dependents survive)
-- DELETE FROM orders WHERE order_id = 42
--   -> SUCCESS (CASCADE removes matching order_items)
-- DELETE FROM shippers WHERE shipper_id = 3
--   -> SUCCESS (SET NULL clears orders.assigned_shipper)

在应用于真实数据之前验证策略

在修改生产约束之前，通过查询pg_constraint或在psql中使用\d读取模式来检查当前动作。识别计划删除的父行有多少依赖行。文档警告说，不带WHERE子句的DELETE FROM products会移除所有行，因此始终限定你的测试删除范围。

在你回滚的事务中运行预期的DELETE。这让你能够观察RESTRICT是否引发错误，CASCADE是否移除了预期数量的子项，或者SET NULL是否留下了带有NULL引用的行，所有这些都不提交更改。只有在确认行为之后，才应在生产中发出ALTER TABLE以更改约束动作。

BEGIN;
DELETE FROM orders WHERE order_id = 42;
SELECT count(*) FROM order_items WHERE order_id = 42;
-- Expect 0 if CASCADE is correct.
ROLLBACK;
-- No data was permanently removed.

知道何时策略不足

这些动作管理当被引用行被删除时对引用行发生的情况。它们不处理任意清理、数据保留时间表或未由外键连接的行的移除。CASCADE不能补偿错误的关系模型；如果子行最初就不应与父行链接，没有删除动作能修复底层模式设计。

文档还指出，ON UPDATE具有相关但不相同的行为，并且在ON UPDATE下不能为SET NULL和SET DEFAULT指定列列表。适用于删除的策略可能不会直接转移到更新。最后，结合CASCADE的无界DELETE语句可以在单个事务中移除大量数据，因此无论约束动作如何，应用程序级别的保护措施和显式WHERE子句仍然是必要的。

-- This single statement could cascade through many tables:
DELETE FROM products;  -- no WHERE clause, caveat programmer
-- If CASCADE is set on multiple levels, this can be destructive.