PostgreSQL级联删除关联行误删全表问题排查

两个删除语句均缺少跨表关联条件，触发了PostgreSQL DELETE ... USING 语法的笛卡尔积匹配逻辑：

• 删除engine_labeledimageattributeval的语句仅声明了关联表engine_labeledimage，但未添加关联条件engine_labeledimageattributeval.image_id = engine_labeledimage.id。只要engine_labeledimage表中存在任意符合job_id IN (3981,3983,3984)的行，主表engine_labeledimageattributeval的所有行都会被命中，最终全表被删除。
• 删除engine_labeledimage的语句仅声明了关联表engine_job，但未添加关联条件engine_labeledimage.job_id = engine_job.id。只要engine_job表中存在任意符合id IN (3981,3983,3984)的行，主表engine_labeledimage的所有行都会被命中，最终全表被删除。

写法1：使用USING并补充关联条件 #

-- 先删除关联属性值
DELETE FROM engine_labeledimageattributeval 
USING engine_labeledimage 
WHERE 
  engine_labeledimageattributeval.image_id = engine_labeledimage.id
  AND engine_labeledimage.job_id IN (3981,3983,3984);

-- 再删除图片数据
DELETE FROM engine_labeledimage
USING engine_job
WHERE 
  engine_labeledimage.job_id = engine_job.id
  AND engine_job.id IN (3981,3983,3984);

写法2：使用子查询（语法更简单，不易漏写关联条件） #

-- 先删除关联属性值
DELETE FROM engine_labeledimageattributeval 
WHERE image_id IN (
  SELECT id FROM engine_labeledimage WHERE job_id IN (3981,3983,3984)
);

-- 再删除图片数据，无需关联engine_job表直接过滤
DELETE FROM engine_labeledimage
WHERE job_id IN (3981,3983,3984);

正式执行删除前，先执行等价的SELECT语句验证命中行数，确认符合预期后再执行删除操作，示例验证语句：

-- 验证待删除的属性值行数
SELECT COUNT(*) FROM engine_labeledimageattributeval 
WHERE image_id IN (
  SELECT id FROM engine_labeledimage WHERE job_id IN (3981,3983,3984)
);

-- 验证待删除的图片行数
SELECT COUNT(*) FROM engine_labeledimage WHERE job_id IN (3981,3983,3984);

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