如何对已创建的表使用ALTER语句添加Delete Cascade级联删除规则

方案1：未建表时直接修改建表语句 #

修改task表的project字段外键定义，直接追加ON DELETE CASCADE即可：

CREATE TABLE task (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    account INTEGER REFERENCES account(id) NOT NULL,
    -- 新增ON DELETE CASCADE配置
    project INTEGER REFERENCES project(id) ON DELETE CASCADE NOT NULL,
    task TEXT NOT NULL,
    cycle INTEGER NOT NULL,
    date_published TIMESTAMPTZ DEFAULT now() NOT NULL
);

方案2：已建表时用事务修改外键约束 #

PostgreSQL不支持直接修改现有外键的级联属性，需要先删除旧约束再新增带级联删除的约束，用事务包裹可以保证操作原子性，避免中途出错导致约束丢失：

BEGIN;
-- 删除原有外键约束，默认命名为task_project_fkey，若不符合可先查询约束名
ALTER TABLE task DROP CONSTRAINT IF EXISTS task_project_fkey;
-- 新增带级联删除的外键约束
ALTER TABLE task
ADD CONSTRAINT task_project_fkey
FOREIGN KEY (project) REFERENCES project(id) ON DELETE CASCADE;
COMMIT;

如果不确定现有外键的约束名，可执行以下语句查询：

SELECT conname FROM pg_constraint 
WHERE conrelid = 'task'::regclass 
AND confrelid = 'project'::regclass;

效果说明 #

配置完成后，删除project表的任意记录时，数据库会自动删除task表中所有关联该项目的记录，无需额外业务代码处理。如果需要删除账户时自动级联删除关联的项目、任务，可按照相同逻辑给project表的account外键、task表的account外键也添加ON DELETE CASCADE属性。

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