PostgreSQL의 NOT VALID 외래 키는 기존 행에 대한 초기 검사를 건너뛰면서 새 참조는 계속 검증합니다. 일치하지 않는 기존 행은 수정하고 검증할 때까지 남아 있습니다. PostgreSQL 17: ALTER TABLE

이 가이드는 Terminal에서 Mac을 사용하는 개발자가 데이터가 채워진 테이블에 외래 키를 추가하고 그 결과를 이해하는 데 도움이 됩니다. 완전한 node-postgres 예제는 합성 문서 데이터만 사용하며, 제약 조건 생성부터 수정과 검증 성공까지 일치하지 않는 참조 하나를 따라갑니다.

기존 행과 새 쓰기에는 어떤 일이 일어나나요?

두 테이블을 생각해 보겠습니다. articles에는 문서 ID와 제목이 들어 있고, article_links에는 해당 ID를 참조하는 링크가 들어 있습니다. 링크의 null이 아닌 article_id에 해당하는 문서가 없으면 그 링크는 일치하지 않는 상태입니다. NOT VALID로 외래 키를 추가하면 이후 쓰기에 제약 조건을 적용하면서도 기존의 불일치는 남겨 둘 수 있습니다. PostgreSQL 17: ALTER TABLE

제약 조건 추가 후 작업 이 예제의 결과
일치하지 않는 기존 링크를 그대로 둠 저장된 상태로 남음
기존 문서를 참조하는 링크를 삽입함 성공함
없는 문서를 참조하는 링크를 삽입함 실패함
링크의 문서 ID를 다른 없는 ID로 변경함 실패함
일치하지 않는 링크가 남은 상태에서 검증함 검증 실패
일치하지 않는 참조를 모두 수정한 후 검증함 검증 성공 가능

이 결과는 제약 조건 추가와 검증을 별개의 단계로 구분하는 문서화된 동작에 따른 것입니다. PostgreSQL 17: ALTER TABLE

외래 키 값이 그대로 유지되는 업데이트는 중요한 예외입니다. PostgreSQL 18에서는 이전 키와 새 키가 같다고 비교되면 구현상 외래 키 검사를 건너뛸 수 있습니다. 따라서 오래된 일치하지 않는 링크의 레이블만 바꾸면 참조를 수정하지 않고도 성공할 수 있습니다. 구현에는 현재 트랜잭션에서 삽입한 행에 대한 특수 처리도 있으므로, 이를 모든 업데이트 순서에 일반화해서는 안 됩니다. PostgreSQL 18 외래 키 구현

따라서 레이블 수정이 성공했다고 해서 정리가 끝났다고 판단하기 어렵습니다. 일치하지 않는 참조를 직접 확인하고, 테이블 전체에서 제약 조건이 충족되는지 검증을 통해 확인하세요.

이 작업에 필요한 PostgreSQL 버전과 테이블 구조는 무엇인가요?

아래 예제는 파티션되지 않은 일반 테이블을 사용하며 PostgreSQL 17 및 18을 대상으로 합니다. 파티션 테이블에는 중요한 버전 차이가 있습니다. PostgreSQL 17에서는 파티션 테이블의 외래 키를 NOT VALID로 선언하는 것이 명시적으로 금지되어 있고, PostgreSQL 18에서 이 기능이 추가되었습니다. 설치된 Node 패키지로 지원 여부를 추측하지 말고 서버 버전을 확인하세요. PostgreSQL 17: ALTER TABLE, PostgreSQL 18 릴리스 노트

PostgreSQL 17에서 참조하는 쪽 테이블이 파티션 테이블이라면, 지원되는 직접 경로는 참조를 수정하고 외래 키를 일반 방식으로 추가하여 초기 검사를 수행하도록 하는 것입니다. 파티션 테이블의 상위 테이블에서 단계별 검증이 꼭 필요하다면 PostgreSQL 18에서 해당 기능을 사용할 수 있습니다. 이 튜토리얼에서는 파티션 계층의 세부 사항이 행 동작을 흐리지 않도록 간단한 테이블 구조를 사용합니다.

참조되는 키에도 적절한 고유성 보장이 필요합니다. 여기서 articles.article_id은 기본 키입니다. 참조하는 열은 의도적으로 NULL를 허용합니다. 기본 MATCH SIMPLE 규칙에서는 null 참조에 대응하는 문서가 없어도 됩니다. 모든 링크가 문서를 가리켜야 한다면 NOT NULL을 별도로 추가하세요. PostgreSQL 18: CREATE TABLE

Mac에서 완전한 합성 예제를 실행하기

실행 중인 PostgreSQL 데이터베이스, 이미 설정된 로컬 연결 구성, 그리고 node-postgres가 지원하는 Node.js 버전이 필요합니다. Terminal에서 프로젝트 디렉터리로 이동해 아래 명령으로 패키지를 설치한 다음, 전체 프로그램을 not-valid-demo.mjs로 저장하세요. 공식 node-postgres 소개 문서에서 패키지 설치 방법을 확인할 수 있습니다. node-postgres 문서

npm install pg

이 프로그램은 new pg.Client()을 사용하며, 이 모듈은 PGHOST, PGPORT, PGDATABASE, PGUSER 같은 PostgreSQL 연결 환경 변수를 읽습니다. 로컬 데이터베이스에 이미 연결되는 설정을 사용하세요. node-postgres: 연결

두 테이블은 임시 테이블이며 이 연결의 세션에서만 존재합니다. PostgreSQL에서는 임시 테이블의 외래 키가 임시 테이블을 참조해야 하므로 프로그램은 양쪽 테이블을 함께 만듭니다. PostgreSQL 18: CREATE TABLE

각 작업은 별도로 기다리는 쿼리를 사용합니다. 값은 $1, $2 같은 node-postgres 매개변수로 전달하고, 테이블 및 제약 조건 이름은 SQL에 고정되어 있습니다. node-postgres: 쿼리

import pg from 'pg';

const client = new pg.Client({ connectionTimeoutMillis: 5000 });
const validate = `ALTER TABLE pg_temp.article_links
  VALIDATE CONSTRAINT article_links_article_fk`;

async function expectForeignKeyError(label, sql, values = []) {
  try {
    await client.query(sql, values);
  } catch (error) {
    if (error.code !== '23503' ||
        error.constraint !== 'article_links_article_fk') throw error;
    console.log(label + ': 23503 (expected)');
    return;
  }
  throw new Error(label + ': unexpectedly succeeded');
}

async function showStatus(label) {
  const result = await client.query(`
    SELECT conname, convalidated
    FROM pg_constraint
    WHERE conrelid = 'pg_temp.article_links'::regclass
      AND conname = 'article_links_article_fk'
      AND contype = 'f'
  `);
  console.log(label, result.rows);
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows);
  await client.query(`CREATE TEMP TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
  )`);
  await client.query(`CREATE TEMP TABLE article_links (
    link_id integer PRIMARY KEY,
    article_id integer,
    label text NOT NULL
  )`);
  await client.query(
    'INSERT INTO pg_temp.articles VALUES ($1, $2)',
    [1, 'Desk arrangement']
  );
  await client.query(`INSERT INTO pg_temp.article_links VALUES
    (10, 1, 'Matching article'),
    (11, 99, 'Older unmatched reference')`);
  await client.query(`ALTER TABLE pg_temp.article_links
    ADD CONSTRAINT article_links_article_fk
    FOREIGN KEY (article_id)
    REFERENCES pg_temp.articles (article_id) NOT VALID`);
  await showStatus('After adding');

  await expectForeignKeyError(
    'New unmatched reference',
    'INSERT INTO pg_temp.article_links VALUES ($1, $2, $3)',
    [12, 99, 'New reference']
  );
  await expectForeignKeyError(
    'Changed unmatched reference',
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [98, 11]
  );
  await client.query(
    'UPDATE pg_temp.article_links SET label = $1 WHERE link_id = $2',
    ['Revised label', 11]
  );
  console.log('Label-only update succeeded');

  const unmatched = await client.query(`
    SELECT l.link_id, l.article_id, l.label
    FROM pg_temp.article_links AS l
    WHERE l.article_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM pg_temp.articles AS a
        WHERE a.article_id = l.article_id
      )
    ORDER BY l.link_id
  `);
  console.log('Unmatched references', unmatched.rows);
  await expectForeignKeyError('Validation before repair', validate);
  await showStatus('After failed validation');

  await client.query(
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [1, 11]
  );
  await client.query(validate);
  await showStatus('After successful validation');
} finally {
  await client.end();
}

저장한 프로그램을 같은 프로젝트 디렉터리에서 실행하세요.

node not-valid-demo.mjs

준비 과정에서 JavaScript 구문 검사를 통과했습니다. 조사 환경에서는 데이터베이스 실행이 불가능했으므로, 다음 결과는 실제 실행 로그에서 가져온 것이 아니라 문서화된 동작을 바탕으로 예상한 것입니다.

결과를 어떻게 해석해야 하나요?

먼저 제약 조건에서 convalidated이 false로 설정된 것을 확인할 수 있어야 합니다. 카탈로그에서는 이 필드가 제약 조건의 검증 여부를 나타냅니다. 데이터베이스 전체에서 제약 조건 이름이 반드시 고유한 것은 아니므로 쿼리는 테이블 이름과 제약 조건 이름을 모두 기준으로 필터링합니다. PostgreSQL: pg_constraint

다음으로 일치하지 않는 새 삽입과 일치하지 않는 참조 변경은 각각 23503을 반환해야 합니다. PostgreSQL은 이 SQLSTATE를 foreign_key_violation로 정의하며, 현지화된 오류 메시지 텍스트를 비교하기보다 오류 코드를 확인하라고 권장합니다. 이 도우미 함수는 예상한 코드와 제약 조건만 허용하며, 예상하지 못한 오류가 발생하면 프로그램을 중단합니다. PostgreSQL 오류 코드

레이블만 업데이트해도 링크 11는 문서 99를 계속 가리킵니다. 따라서 진단 쿼리는 레이블이 Revised label로 바뀐 해당 링크 하나만 반환해야 합니다. 명시적인 IS NOT NULL 조건은 선택적 null 참조를 제외하고, NOT EXISTS는 articles에 없는 null이 아닌 ID를 찾습니다. 이는 제공된 합성 행에서 도출한 예시 결과입니다.

이 시점에 검증하면 실패해야 합니다. 수정 작업에서 링크 11이 문서 1을 참조하도록 바꾸면 검증에 성공하고 convalidated는 true이 되어야 합니다. VALIDATE CONSTRAINT는 테이블을 스캔해 이전에 검증되지 않은 제약 조건을 확인하며, 자체적으로 데이터를 수정하지는 않습니다. PostgreSQL: ALTER TABLE

이 예제에서 수정은 명시적인 편집상 판단입니다. 두 링크 모두 Desk arrangement를 참조하도록 의도했습니다. 자신의 문서 데이터에서는 일치하지 않는 각 참조를 검토한 뒤 ID를 수정할지, 누락된 문서를 추가할지, 오래된 링크를 삭제할지 결정하세요. 일치하지 않는 모든 ID를 하나의 문서로 자동 연결하면 데이터베이스가 판별할 수 없는 차이를 감추게 됩니다.

데이터가 채워진 테이블에는 이 순서를 어떻게 적용하나요?

세 단계로 진행하세요. NOT VALID 제약 조건을 추가하고 커밋한 다음, 일치하지 않는 참조를 확인하고 수정한 후 검증합니다. 외래 키를 추가하면 두 테이블 모두에 SHARE ROW EXCLUSIVE 잠금이 설정됩니다. 검증은 참조하는 테이블에 SHARE UPDATE EXCLUSIVE 잠금을, 참조되는 테이블에 ROW SHARE 잠금을 설정하여 검증 스캔 중에도 일반적인 업데이트가 동시에 진행되도록 합니다. 그래도 잠금 획득을 기다릴 수 있습니다. PostgreSQL: ALTER TABLE

처음 제약 조건을 추가하는 작업과 나중에 수행하는 검증을 별도 트랜잭션으로 유지하면 단계별 접근의 목적을 살릴 수 있습니다. 긴 스캔이 시작되기 전에 첫 트랜잭션을 끝낼 수 있습니다. 제약 조건 추가는 짧게 수행할 작업으로 예약하고, 진단 쿼리는 정리에 참고하는 도구로 사용하세요. 성공적인 검증이 완료 단계입니다.

이 예제는 예상된 문장별 오류를 개별적으로 처리할 수 있도록 명시적인 트랜잭션 블록을 사용하지 않습니다. 이 순서를 node-postgres 트랜잭션에 적용한다면 BEGIN, COMMIT, ROLLBACK를 직접 실행하고, 트랜잭션의 모든 문장을 같은 클라이언트에서 실행하세요. node-postgres 문서는 트랜잭션 문장에 pool.query()를 사용하지 말라고 명시합니다. node-postgres: 트랜잭션

예제의 세 가지 관찰을 함께 활용하세요. 일치하지 않는 행으로 수정할 대상을 파악하고, 새 참조 거부로 제약 조건이 계속 적용됨을 확인하며, convalidated = true으로 검증 성공을 기록합니다. 이렇게 하면 데이터가 채워진 문서 테이블에 관계를 추가할 때 완료 시점을 명확히 알 수 있습니다.

출처