使用asyncpg删除PostgreSQL行时23503与23502错误码差异的原因及解决

核心原因 #

这种情况并非asyncpg的问题，本质是数据库约束配置或触发器逻辑导致PostgreSQL抛出了NotNullViolationError（错误码23502），而非预期的ForeignKeyViolationError（错误码23503），常见场景有两种：

1. 外键ON DELETE规则为SET NULL，但子表外键字段设为NOT NULL
   删除父表行时，PostgreSQL会按照外键的ON DELETE SET NULL规则，尝试将子表中关联的外键字段设为NULL，但如果该字段被标记为NOT NULL，就会触发非空约束错误（23502），而非外键约束本身的冲突错误（23503）。

2. 自定义触发器/级联逻辑导致非空约束冲突
   如果子表上存在自定义触发器，或者父表删除时触发了额外的级联修改操作，这些逻辑可能会尝试将子表的某个非空字段设为NULL，从而触发23502错误。

解决步骤 #

1. 检查外键约束配置 #

首先查询外键的ON DELETE规则，确认是否存在冲突：

SELECT 
  conname AS 外键约束名,
  confdeltype AS ON_DELETE规则,
  pg_get_constraintdef(c.oid) AS 约束定义
FROM pg_constraint c
WHERE 
  confrelid = '父表名'::regclass  -- 替换为你的父表名
  AND conrelid = '子表名'::regclass  -- 替换为你的子表名
  AND contype = 'f';

confdeltype的对应关系：

• a = NO ACTION
• r = RESTRICT
• c = CASCADE
• n = SET NULL
• d = SET DEFAULT

如果结果显示ON DELETE为SET NULL，同时子表的外键字段是NOT NULL，就是问题根源。

2. 调整外键约束或字段属性 #

根据业务需求选择以下方案：

• 方案一：修改ON DELETE规则
  若需要阻止删除父行（保持数据完整性），改为RESTRICT或NO ACTION（默认行为），此时删除会触发23503错误：

  ALTER TABLE 子表名
  DROP CONSTRAINT 外键约束名,
  ADD CONSTRAINT 外键约束名 
  FOREIGN KEY (外键字段名) REFERENCES 父表名(主键字段名) 
  ON DELETE RESTRICT;
  

  若需要删除父行时自动删除关联子行，改为CASCADE：

  ALTER TABLE 子表名
  DROP CONSTRAINT 外键约束名,
  ADD CONSTRAINT 外键约束名 
  FOREIGN KEY (外键字段名) REFERENCES 父表名(主键字段名) 
  ON DELETE CASCADE;
  

• 方案二：移除子表字段的非空约束
  如果业务允许外键字段为NULL，则去掉NOT NULL限制：

  ALTER TABLE 子表名 ALTER COLUMN 外键字段名 DROP NOT NULL;
  

3. 排查自定义触发器 #

检查子表上的自定义触发器，确认是否存在导致非空约束冲突的逻辑：

SELECT 
  tgname AS 触发器名,
  pg_get_triggerdef(t.oid) AS 触发器定义
FROM pg_trigger t
WHERE 
  tgrelid = '子表名'::regclass
  AND NOT tgisinternal;  -- 排除系统内置触发器

如果触发器在父行删除时修改了子表的非空字段，需要调整触发器逻辑，避免违反约束。

4. 代码层面统一处理错误 #

在asyncpg中，可以同时捕获23502和23503错误码，统一处理外键关联导致的删除失败：

import asyncpg

async def delete_parent_row(conn, parent_id):
    try:
        await conn.execute("DELETE FROM 父表名 WHERE id = $1", parent_id)
    except asyncpg.PostgresError as e:
        if e.sqlcode in ('23502', '23503'):
            # 自定义处理逻辑，比如返回错误提示
            raise ValueError("无法删除该行，存在关联的子数据") from e
        else:
            # 其他错误重新抛出
            raise

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