PostgreSQL中如何避免外键随关联主键删除而被删除？

要实现删除bank_account表的主键记录后，payment_history表仍保留对应account_number外键值的需求，有以下几种可行方案：

方案1：移除外键约束+触发器验证（允许孤儿记录） #

PostgreSQL默认外键约束会阻止删除存在子表引用的父表记录。如果接受子表存在“孤儿”记录（即payment_history的account_number在bank_account中无对应记录），可以移除外键约束，同时用触发器保证插入/更新时的合法性：

1. 删除原有外键约束：

ALTER TABLE payment_history DROP CONSTRAINT account_number_fk;

2. 创建验证函数与触发器，确保插入/更新payment_history时，account_number在bank_account中存在：

-- 定义验证函数
CREATE OR REPLACE FUNCTION validate_account_existence()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM bank_account WHERE account_number = NEW.account_number) THEN
        RAISE EXCEPTION '账户号 % 不存在于bank_account表中', NEW.account_number;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER check_account_validity
BEFORE INSERT OR UPDATE OF account_number ON payment_history
FOR EACH ROW EXECUTE FUNCTION validate_account_existence();

这样设置后，删除bank_account的记录不会影响payment_history的account_number值，同时插入/更新时仍会验证账户的合法性。

方案2：软删除父表记录（推荐，保持数据完整性） #

更合理的做法是不物理删除bank_account的记录，而是通过标记字段实现“软删除”，既保留关联关系，又满足业务需求：

1. 给bank_account表添加软删除标记字段：

ALTER TABLE bank_account ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

2. 当需要删除账户时，更新标记而非删除记录：

UPDATE bank_account SET is_deleted = TRUE WHERE account_number = 目标账户号;

3. 查询有效账户时过滤已删除记录：

SELECT * FROM bank_account WHERE is_deleted = FALSE;

这种方式维持了外键约束的完整性，payment_history的account_number值会永久保留，且始终能关联到对应的账户记录（即使是已标记删除的）。

方案3：设置外键ON DELETE SET NULL（不符合需求，仅作备选） #

如果可以接受payment_history的account_number变为NULL，可修改外键约束：

-- 删除原有外键
ALTER TABLE payment_history DROP CONSTRAINT account_number_fk;

-- 添加带ON DELETE SET NULL的外键
ALTER TABLE payment_history ADD CONSTRAINT account_number_fk
    FOREIGN KEY(account_number) REFERENCES bank_account(account_number)
    ON DELETE SET NULL;

但此方案会在删除父表记录后将子表的account_number设为NULL，不符合你保留原值的需求，仅作为参考。

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