PostgreSQL NOT VALID 외래키인데 새 INSERT는 거절된다

PostgreSQL에서 기존 데이터에 위반 행이 있어 외래키를 NOT VALID로 추가했는데, 새 INSERT는 외래키 오류로 거절될 수 있다. NOT VALID는 제약을 추가할 때 기존 행 검사를 미루는 옵션이다. 이후의 새 입력 검사를 끄지 않는다. 따라서 기존 위반 행이 남아 있으면서 새 위반 행은 들어가지 않는 상태가 가능하다.

PostgreSQL 18의 ALTER TABLE 문서↗ (새 탭에서 열림)는 제약 추가 시 기존 행의 스캔을 생략하고 이후 INSERT·UPDATE에는 제약을 적용한다고 설명한다. 데이터를 정리한 다음 VALIDATE CONSTRAINT로 기존 행까지 검증하는 두 단계다. 새 쓰기가 막히는 것을 해결하려고 VALIDATE를 실행할 필요는 없다. 새 입력의 참조 키부터 확인해야 한다.

먼저 실제 테이블의 검증 상태를 본다

다음 조회는 INSERT 대상 테이블의 외래키가 검증됐는지 확인한다. public.fk_child를 자신의 스키마와 테이블 이름으로 바꿔 실행한다.

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'public.fk_child'::regclass
  AND contype = 'f';

pg_constraint 문서↗ (새 탭에서 열림)에서 contype = 'f'는 외래키, convalidated는 검증 여부다. 제약 이름만으로 찾으면 다른 테이블의 동명 제약과 혼동할 수 있어 대상 테이블도 한정했다. 결과가 0행이면 지정한 테이블에서 외래키를 찾지 못한 것이다.

psql에서 convalidated가 f로 나와도 검사를 하지 않는다는 뜻은 아니다. 기존 행까지 검증 완료로 표시되지 않았다는 뜻이다. 위반 행이 실제로 남아 있는지도 이 값 하나로 알 수 없다. 아래 실험에서는 위반 행을 모두 고친 직후에도 f가 유지됐다.

기존 위반은 남지만 같은 값을 새로 넣으면 실패한다

PostgreSQL 18 서버와 psql, 임시 테이블 생성 권한이 있는 운영과 분리된 실습 DB가 필요하다. 그 DB에 새로 연결한 같은 세션에서 다음 입력을 실행한다. 자동 커밋을 켜고 BEGIN으로 묶지 않는다. 마지막 부분의 의도한 오류 뒤에도 다음 문장을 실행하도록 설정했다. 임시 테이블은 세션이 끝나면 사라진다.

다음 입력은 부모에 없는 999를 먼저 저장한 뒤 NOT VALID 외래키를 추가하고, 같은 참조 값의 새 행과 정상 참조 값의 새 행을 비교한다.

\set AUTOCOMMIT on
\set ON_ERROR_STOP off
CREATE TEMP TABLE fk_parent (id integer PRIMARY KEY);
CREATE TEMP TABLE fk_child (
  id integer PRIMARY KEY,
  parent_id integer NOT NULL
);
INSERT INTO fk_parent VALUES (1);
INSERT INTO fk_child VALUES (10, 999);

ALTER TABLE fk_child ADD CONSTRAINT child_parent_fk
  FOREIGN KEY (parent_id) REFERENCES fk_parent(id) NOT VALID;

INSERT INTO fk_child VALUES (11, 999);
INSERT INTO fk_child VALUES (12, 1);

새 격리 컨테이너의 PostgreSQL·psql 18.6에서 실행했다. 외래키 추가는 ALTER TABLE로 성공했다. 하지만 (11, 999) INSERT는 다음 오류로 실패했고, (12, 1) INSERT는 INSERT 0 1로 성공했다. 다음은 실패 출력의 핵심 부분이다.

ERROR:  insert or update on table "fk_child" violates foreign key constraint "child_parent_fk"
DETAIL:  Key (parent_id)=(999) is not present in table "fk_parent".

새 INSERT가 실패한 뒤 테이블에는 (10, 999)와 (12, 1)만 남았다. 제약 추가 전에 넣은 첫 행이 자동으로 수정되거나 삭제되지는 않았다. 별도 대조로 같은 기존 데이터에 NOT VALID 없이 외래키를 추가하면 그 단계에서 실패했다. 정상 행의 parent_id를 888로 바꾸는 UPDATE도 실패했다. 이 오류들은 모두 외래키 위반 SQLSTATE인 23503↗ (새 탭에서 열림)이었다.

이후 실험의 상태를 확인하려면 맨 앞 조회의 대상을 public.fk_child에서 pg_temp.fk_child로 바꾼다. 이 시점의 child_parent_fk는 convalidated = f였다. 같은 실험에서 새 위반 INSERT가 성공한다면 입력 대상과 실제 제약 정의를 먼저 대조한다. 오류 뒤 모든 조회가 25P02로 실패한다면 자동 커밋 조건과 달리 트랜잭션 전체가 실패 상태인지 확인한다.

VALIDATE는 위반 행을 고쳐 주지 않는다

다음 입력은 검증을 시도한 뒤, 이 예제에서 부모를 찾지 못하는 자식 행을 조회한다. parent_id가 NOT NULL인 단일 열 외래키이므로 이 조회로 해당 위반 행을 찾을 수 있다.

ALTER TABLE fk_child VALIDATE CONSTRAINT child_parent_fk;

SELECT c.id, c.parent_id
FROM fk_child AS c
WHERE NOT EXISTS (
  SELECT 1 FROM fk_parent AS p WHERE p.id = c.parent_id
);

첫 VALIDATE는 parent_id = 999가 부모에 없다는 23503 오류로 실패했다. 뒤의 조회는 id = 10, parent_id = 999 한 행을 반환했고, 제약 상태는 여전히 f였다. VALIDATE의 역할↗ (새 탭에서 열림)은 제약에 맞지 않는 행이 없는지 확인하는 것이다. 지정한 제약을 테이블 단위로 검증하므로, 정리한 행 외에 남은 위반 행이 있으면 다시 실패할 수 있다. 검증 실패를 데이터 정리 완료로 받아들이면 안 된다.

이 합성 데이터에서는 id = 10이 부모 1을 참조해야 한다고 정하고 그 한 행만 고쳤다. 다음 입력은 수정 직후와 재검증 직후의 상태를 나란히 확인한다.

UPDATE fk_child SET parent_id = 1 WHERE id = 10;
SELECT convalidated FROM pg_constraint
WHERE conrelid = 'pg_temp.fk_child'::regclass
  AND conname = 'child_parent_fk';

ALTER TABLE fk_child VALIDATE CONSTRAINT child_parent_fk;
SELECT convalidated FROM pg_constraint
WHERE conrelid = 'pg_temp.fk_child'::regclass
  AND conname = 'child_parent_fk';

실제 결과는 UPDATE 1 → 첫 조회 f → ALTER TABLE 성공 → 두 번째 조회 t였다. 수정 후 위반 행 수는 0이었지만, 데이터가 맞아진 것과 카탈로그에 검증 완료가 기록되는 것은 별개였다. 이후에도 부모 없는 값을 넣는 INSERT는 같은 외래키 오류로 거절됐다.

기존 데이터에서 같은 상황을 만나면 먼저 부모를 찾지 못하는 행을 조회하고, 참조 값이 잘못됐는지 부모 데이터가 누락됐는지 업무 기준으로 판단한다. 이 예제의 1로 일괄 변경하는 것은 해결책이 아니다. 정리가 끝난 뒤 별도로 VALIDATE를 수행하고 성공 여부와 검증 상태를 확인한다.

이번 관찰은 새 임시 DB의 작은 임시 테이블과 단일 integer·NOT NULL 외래키에 한정한다. 복합 키, nullable 참조 열, 파티션과 복제 설정을 바꾼 환경은 실험하지 않았다. 공식 문서에는 제약 추가·검증 단계의 잠금이 설명돼 있지만, 대용량 테이블의 소요 시간과 동시 쓰기 경합은 측정하지 않았다. NOT VALID를 운영에서 잠금 없이 적용할 수 있다는 의미로 해석해서는 안 된다.