PostgreSQL ON DELETE SET NULL인데 부모 삭제가 실패한다

PostgreSQL에서 외래키를 ON DELETE SET NULL로 선언했는데 부모 삭제가 23502로 실패한다면, 오류가 가리키는 자식 열의 NOT NULL을 확인한다. SET NULL은 부모를 지울 때 자식의 참조 값을 NULL로 바꾸는 동작이다. 그 열이 NULL을 허용하도록 바꾸어 주는 설정은 아니다.

자식을 부모 없이 남겨도 되는 관계인지부터 판단해야 한다. 선택 관계라면 참조 열의 NULL 허용과 SET NULL을 함께 검토하고, 필수 관계라면 NOT NULL을 유지하면서 참조 중인 부모 삭제를 막는 정책을 검토한다. PostgreSQL 18 외래키 문서↗ (새 탭에서 열림)도 삭제 동작에 다른 제약이 그대로 적용되며, 관계의 의미에 따라 정책을 고르도록 설명한다.

부모 DELETE가 자식 열의 NOT NULL에 걸린다

먼저 삭제 오류의 SQLSTATE와 테이블·열 이름을 확인한다. 다음은 별도 합성 실습에서 DELETE FROM sn_lab.parent WHERE id = 1;을 실행했을 때 받은 오류의 핵심 부분이다. 부모를 지웠지만 메시지는 자식 child_required.parent_id를 가리킨다.

ERROR:  23502: null value in column "parent_id" of relation "child_required" violates not-null constraint
DETAIL:  Failing row contains (10, null).

Failing row는 자식 10번이 NULL로 저장됐다는 뜻이 아니다. 저장하려던 값이 거절됐다는 뜻이다. 같은 오류의 CONTEXT에는 자식 참조 열을 NULL로 바꾸려는 내부 UPDATE가 나타났다. 이 갱신이 실패하면서 부모 DELETE도 실패했다.

공식 SQLSTATE 목록↗ (새 탭에서 열림)에서 23502는 not_null_violation, 23503은 foreign_key_violation이다. 부모 삭제가 실패했다는 이유만으로 모두 외래키 위반으로 묶지 않는다. 클라이언트가 번역된 메시지만 보여 준다면 상세 오류에서 코드와 테이블·열 정보를 찾는다.

실제 DB의 삭제 동작과 열 제약을 함께 읽는다

다음 두 SELECT는 현재 DB에 저장된 외래키 정의와 오류가 지목한 열의 NULL 허용 여부를 확인한다. sn_lab, child_required, parent_id를 자신의 스키마·자식 테이블·열 이름으로 바꾸고, 해당 테이블을 조회할 수 있는 계정으로 실행한다. 명시적 트랜잭션 안에서 DELETE가 실패했다면 먼저 그 트랜잭션을 롤백하거나 오류 전에 만든 적절한 저장점으로 복구해야 조회할 수 있다. 저장점 복구는 그 지점 이후의 작업을 되돌리는 동작↗ (새 탭에서 열림)이다.

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'sn_lab.child_required'::regclass
  AND contype = 'f';

SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_schema = 'sn_lab'
  AND table_name = 'child_required'
  AND column_name = 'parent_id';

실습의 첫 조회는 child_required_parent_fk의 정의로 FOREIGN KEY (parent_id) REFERENCES sn_lab.parent(id) ON DELETE SET NULL을 반환했다. 두 번째는 parent_id의 is_nullable이 NO였다. SET NULL이 값을 바꾸려는 바로 그 열이 NULL을 허용하지 않았다. 마이그레이션 파일의 의도보다 실제 연결된 DB의 정의를 먼저 대조할 이유다.

pg_constraint↗ (새 탭에서 열림)의 contype = 'f'는 외래키를 고르며, pg_get_constraintdef↗ (새 탭에서 열림)는 저장된 제약 정의를 재구성한다. 외래키가 여러 개 나오면 오류 열을 포함하는 정의를 찾는다. information_schema.columns↗ (새 탭에서 열림)는 현재 계정이 접근할 수 있는 열만 보여 준다. 조회가 0행이거나 테이블을 찾지 못하면 DB·스키마·이름·권한을 확인한다. 빈 결과를 NULL 허용의 증거로 삼지 않는다. YES인데도 23502라면 오류가 지목한 열부터 다시 대조한다. NULL 허용만으로 다른 제약이나 트리거까지 통과하는 것은 아니다.

NULL을 허용한 쪽에서는 자식 행이 남았다

PostgreSQL 18.6의 새 실습 스키마에 두 자식 모두 SET NULL 외래키를 두고, child_required.parent_id에만 NOT NULL을 붙여 비교했다. 필수 참조 자식 (id=10, parent_id=1)과 선택 참조 자식 (id=20, parent_id=2)를 넣었다.

• 부모 1번 삭제는 23502로 실패했다. 오류 전에 만든 저장점으로 복구한 뒤에도, 커밋한 뒤에도 부모 1번과 자식 (10, 1)이 그대로 남았다. 부모 삭제나 자식의 NULL 갱신이 일부만 저장되지는 않았다.
• 부모 2번 삭제는 DELETE 1, SQLSTATE 00000으로 성공했다. 커밋 후 부모 2번은 없고 자식 20번은 남았으며 parent_id만 NULL이었다.
• 어느 자식도 참조하지 않는 부모 3번도 삭제됐다. 이 조합이 모든 부모 삭제를 막는 것은 아니었다.

수정 방향은 자식을 어떻게 남길지에 달려 있다

부모 없이 자식을 남겨도 된다면 NULL을 허용하는 참조 열과 SET NULL이 맞는 조합이다. 예를 들어 담당자가 삭제돼도 기록을 보존해야 한다면, 연결이 없는 상태를 애플리케이션의 입력·조회·화면도 처리할 수 있어야 한다. 이를 확인한 뒤 참조 열의 NULL 허용을 스키마 변경안에 반영한다. 오류를 없애려고 운영 제약부터 해제하지 않는다.

자식에게 부모가 반드시 있어야 한다면 NOT NULL을 유지하고 참조 중인 부모 삭제를 거절하는 RESTRICT 또는 기본 비지연 NO ACTION을 검토한다. NO ACTION은 외래키를 지연 검사하도록 선언한 경우 검사 시점이 달라질 수 있다. 부모를 꼭 제거해야 한다면 자식을 유효한 다른 부모로 재배정하는 등 업무 처리를 먼저 정한다.

부모와 함께 자식까지 폐기해야 하는 관계에는 CASCADE를 검토할 수 있다. 다만 이는 자식 행도 지우는 정책이다. SET NULL의 오류를 피하려고 일괄 교체할 옵션으로 삼지 않는다. 이 선택 기준은 공식 문서의 삭제 정책 설명↗ (새 탭에서 열림)에 따른 것이며, 이번 실습에서 정책 변경이나 업무 데이터 이관을 실행한 결과는 아니다.

직접 확인한 범위는 단일 열 integer 외래키, 사용자 트리거 없는 합성 테이블과 한 연결이다. 복합 외래키·여러 참조 관계·동시 삭제·다른 PostgreSQL 버전·ORM의 처리는 검증하지 않았다.