PostgreSQL健身追踪数据库中模板与动作的删除处理咨询

针对你遇到的问题，核心是要避免删除Template或Movement后，关联的已完成训练/练习记录丢失关键名称信息，同时避免出现无效外键ID。这里提供两种可行方案，优先推荐第一种直接匹配你的需求：

方案一：在已完成记录中存储名称副本（推荐） #

这种方式通过在CompletedWorkout和CompletedExercise中分别存储模板、动作的名称副本，彻底解除已完成记录对原模板/动作的依赖，确保删除原记录后名称依然可显示。

步骤1：修改CompletedWorkout表 #

添加template_name字段存储模板名称，同时调整外键行为允许删除后设为NULL：

-- 添加模板名字段
ALTER TABLE CompletedWorkout ADD COLUMN template_name varchar(255) NOT NULL;

-- 先移除原有外键约束（需先通过\d CompletedWorkout查询约束名，替换下面的约束名）
ALTER TABLE CompletedWorkout DROP CONSTRAINT completedworkout_template_id_fkey;

-- 允许template_id为NULL
ALTER TABLE CompletedWorkout ALTER COLUMN template_id DROP NOT NULL;

-- 重新添加外键，设置删除后将template_id设为NULL
ALTER TABLE CompletedWorkout ADD CONSTRAINT completedworkout_template_id_fkey 
FOREIGN KEY (template_id) REFERENCES Template(id) ON DELETE SET NULL;

步骤2：用触发器自动同步模板名称 #

创建触发器，确保插入CompletedWorkout时自动从Template复制名称，无需应用层手动处理：

-- 创建同步函数
CREATE OR REPLACE FUNCTION sync_template_name()
RETURNS TRIGGER AS $$
BEGIN
    SELECT name INTO NEW.template_name FROM Template WHERE id = NEW.template_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定插入触发器
CREATE TRIGGER trigger_completedworkout_insert
BEFORE INSERT ON CompletedWorkout
FOR EACH ROW
EXECUTE FUNCTION sync_template_name();

可选：如果希望模板名称更新时，已完成训练的名称也同步更新，添加更新触发器：

CREATE OR REPLACE FUNCTION update_template_name()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE CompletedWorkout SET template_name = NEW.name WHERE template_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_template_update
AFTER UPDATE OF name ON Template
FOR EACH ROW
EXECUTE FUNCTION update_template_name();

步骤3：对CompletedExercise和Movement执行相同操作 #

-- 添加动作名字段
ALTER TABLE CompletedExercise ADD COLUMN movement_name varchar(255) NOT NULL;

-- 调整外键约束
ALTER TABLE CompletedExercise DROP CONSTRAINT completedexercise_movement_id_fkey;
ALTER TABLE CompletedExercise ALTER COLUMN movement_id DROP NOT NULL;
ALTER TABLE CompletedExercise ADD CONSTRAINT completedexercise_movement_id_fkey 
FOREIGN KEY (movement_id) REFERENCES Movement(id) ON DELETE SET NULL;

-- 同步动作名称的触发器
CREATE OR REPLACE FUNCTION sync_movement_name()
RETURNS TRIGGER AS $$
BEGIN
    SELECT name INTO NEW.movement_name FROM Movement WHERE id = NEW.movement_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_completedexercise_insert
BEFORE INSERT ON CompletedExercise
FOR EACH ROW
EXECUTE FUNCTION sync_movement_name();

方案二：软删除（标记删除而非物理删除） #

如果不想存储冗余数据，可给Template和Movement添加deleted_at字段，标记记录为“已删除”，而非物理删除。这样外键关联依然有效，已完成记录可正常获取名称，仅在查询可用模板/动作时过滤已删除记录。

步骤1：给模板和动作表添加删除标记字段 #

ALTER TABLE Template ADD COLUMN deleted_at timestamp;
ALTER TABLE Movement ADD COLUMN deleted_at timestamp;

步骤2：修改删除逻辑 #

不再执行DELETE语句，而是用UPDATE标记删除：

-- 删除模板
UPDATE Template SET deleted_at = CURRENT_TIMESTAMP WHERE id = [目标ID];

-- 删除动作
UPDATE Movement SET deleted_at = CURRENT_TIMESTAMP WHERE id = [目标ID];

步骤3：查询可用模板/动作时过滤已删除记录 #

-- 查询用户可用的训练模板
SELECT * FROM Template WHERE user_id = [用户ID] AND deleted_at IS NULL;

-- 查询用户可用的动作
SELECT * FROM Movement WHERE user_id = [用户ID] AND deleted_at IS NULL;

两种方案对比 #

根据你的需求（删除模板后已完成训练仍显示原名称），方案一是最直接的选择，能确保名称不受后续模板删除或修改的影响。

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