如何在PostgreSQL中删除关联部门时抛出自定义错误而非默认外键报错

问题根因 #

PostgreSQL的外键约束本质是系统内置的触发器，执行优先级高于用户自定义的普通BEFORE DELETE触发器。所以你删除部门时，系统先触发外键约束检查，抛出系统错误，你的自定义触发器根本没有执行的机会。

解决方案 #

方案1：调整外键约束为延迟检查（推荐） #

该方案保留外键约束的数据一致性能力，同时让自定义触发器先执行抛出业务错误：

1. 修改现有外键约束，设置为事务提交时再检查：

ALTER TABLE Employees 
DROP CONSTRAINT employees_did_fkey,
ADD CONSTRAINT employees_did_fkey 
  FOREIGN KEY (did) REFERENCES Departments(did)
  DEFERRABLE INITIALLY DEFERRED;

2. 创建自定义触发器和对应函数：

-- 触发器校验函数
CREATE OR REPLACE FUNCTION check_dept_delete()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM Employees WHERE did = OLD.did) THEN
    RAISE EXCEPTION '删除失败：该部门下仍有关联在职员工，无法删除';
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到部门表
CREATE TRIGGER trg_before_dept_delete
BEFORE DELETE ON Departments
FOR EACH ROW EXECUTE FUNCTION check_dept_delete();

方案2：移除外键约束完全用触发器实现校验 #

如果不需要外键约束的其他校验能力，也可以直接删除employees_did_fkey外键，仅保留上面的自定义触发器即可，不推荐该方案，因为会失去新增/修改员工时的部门合法性校验能力，需要额外补充其他触发器做数据一致性保障。

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