PostgreSQL更新违反非空约束问题排查与解决

核心问题 #

你的更新语句中，子查询未匹配到行时会返回NULL，这是触发NOT NULL约束报错的直接原因：

• 当tb_provaDoCandidato中的某行在tb_questoesProvaDoCandidato里找不到满足numeroDaProva、idQuestao、idAlternativa = respostaIdAlternativa三个条件的记录时，子查询返回NULL，而主表的respostaCandidato字段带有NOT NULL约束，因此触发报错。
• 错误信息中标记的NULL是更新操作试图写入的值，并非原表的当前值；你看到的原表0是更新前的字段值，更新时被尝试替换为NULL才导致报错。

针对你困惑的两点：

1. 此前语句运行正常与PgAdmin配置无关，大概率是之前tb_provaDoCandidato的所有行都能在子表找到匹配记录，后续数据新增或修改导致部分行无匹配。
2. 移除约束后出现的NULL值，并非子表respostaCandidato=0导致，而是这些行本身在子表中无匹配记录，子查询返回NULL从而写入主表。

解决方案 #

方案1：仅更新有匹配的行 #

使用FROM子句关联两张表，只处理能在子表找到匹配的行，避免NULL写入：

UPDATE public."tb_provaDoCandidato" pc
SET "respostaCandidato" = qp."respostaCandidato"
FROM public."tb_questoesProvaDoCandidato" qp
WHERE pc."numeroDaProva" = qp."numeroDaProva" 
  AND pc."idQuestao" = qp."idQuestao"
  AND pc."idAlternativa" = qp."respostaIdAlternativa";

这种写法比子查询更高效，且仅对有匹配的行执行更新，不会触发NOT NULL约束问题。

方案2：无匹配时保留原字段值 #

如果需要处理所有行，无匹配的行保留原respostaCandidato值，可使用COALESCE函数：

UPDATE public."tb_provaDoCandidato" pc
SET "respostaCandidato" = COALESCE(
  (SELECT qp."respostaCandidato" FROM public."tb_questoesProvaDoCandidato" qp
   WHERE pc."numeroDaProva" = qp."numeroDaProva" 
     AND pc."idQuestao" = qp."idQuestao"
     AND pc."idAlternativa" = qp."respostaIdAlternativa"),
  pc."respostaCandidato"
);

COALESCE会优先取子查询结果，若子查询返回NULL（无匹配），则使用原字段值，避免违反NOT NULL约束。

方案3：补全无匹配的数据 #

如果业务逻辑要求tb_provaDoCandidato的所有行都必须在子表有对应记录，先找出无匹配的行并补全子表数据：

-- 查询主表中在子表无匹配的行
SELECT pc.*
FROM public."tb_provaDoCandidato" pc
LEFT JOIN public."tb_questoesProvaDoCandidato" qp
  ON pc."numeroDaProva" = qp."numeroDaProva" 
  AND pc."idQuestao" = qp."idQuestao"
  AND pc."idAlternativa" = qp."respostaIdAlternativa"
WHERE qp."respostaCandidato" IS NULL;

补全这些行对应的子表记录后，再执行原更新语句即可正常运行。

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