문서 ID가 두 참조 열 중 어느 쪽에든 두 번 이상 나타나지 않도록 하려면, 두 역할을 하나의 고유 문서 ID를 가진 참조 테이블에 넣으세요. 각 행에서 정확히 한 열만 채워지도록 허용한다면 고유 표현식 인덱스가 더 간단한 방법입니다.

이 튜토리얼은 PostgreSQL 및 node-postgres를 사용해 macOS Terminal에서 작업하는 개발자를 위한 것입니다. 목표는 문서 참조 전체에 공통 풀을 적용하는 것입니다. 문서가 어느 역할에서든 한 번 참조되면, 해당 문서에 대한 다른 참조는 실패해야 합니다. 모든 예시 행은 임의로 만든 데이터입니다.

두 열을 통틀어 고유하다는 것은 무슨 뜻인가요?

두 열 모두 같은 문서 카탈로그를 참조한다고 가정합니다. 의도한 규칙은 두 위치를 포함해 모든 참조 위치에서 null이 아닌 각 문서 ID가 최대 한 번만 나타나는 것입니다. 이는 각 열을 별도로 고유하게 만드는 것보다 더 엄격합니다.

묶음 기본 문서 보조 문서 의도한 규칙에 따른 결과
1 101 102 허용
2 103 101 거부: 101은 이미 묶음 1에 나타남
3 104 104 거부: 같은 문서가 두 위치를 차지함

복합 UNIQUE (primary_article_id, secondary_article_id)는 조합을 고유하게 만듭니다. 별도의 고유 제약 조건은 각 열을 독립적으로 고유하게 만듭니다. 어느 설계도 묶음 1과 2 사이의 열 간 중복을 거부하지 않습니다. PostgreSQL 문서에 따르면 다중 열 고유 인덱스는 모든 인덱스 값이 일치할 때 중복을 거부합니다. 고유 인덱스

삽입 쿼리를 작성하기 전에 허용할 행 형태를 기준으로 설계를 선택하세요.

필요한 행 형태 적합한 설계
두 참조가 모두 채워질 수 있음 참조를 행으로 저장하고 article_id를 고유하게 설정
참조를 정확히 하나만 채워야 함 행 검사로 정확히 하나를 적용하고 COALESCE를 고유 인덱스로 설정
참조를 0개 또는 1개 채울 수 있음 최대 하나 검사와 같은 표현식 인덱스 사용
열이 서로 다른 카탈로그를 참조함 같은 숫자 ID가 같은 대상을 의미하는지 먼저 정의하세요. 이 튜토리얼에서는 하나의 공유 카탈로그를 가정합니다.

두 참조를 모두 허용할 때는 어떻게 규칙을 적용하나요?

각 참조를 묶음, 역할, 문서 ID를 포함하는 행으로 표현하세요. 역할은 원래 열의 의미를 유지합니다. article_id에 단일 고유 제약 조건을 설정하면 두 역할을 모두 포괄합니다.

아래 스키마는 일반적인 PostgreSQL 제약 조건을 결합합니다. 외래 키는 일치하는 카탈로그 행이 있어야 하도록 하고, 복합 기본 키는 묶음과 슬롯마다 참조를 하나씩만 허용하며, 이름이 지정된 고유 제약 조건은 각 문서가 한 번만 사용되도록 합니다. 이러한 제약 조건 기능은 제약 조건 문서에 설명되어 있습니다.

재현 가능한 예시 만들기

PostgreSQL 14–18과 사용 가능한 psql 명령이 있는 비어 있는 로컬 예제 데이터베이스를 사용하세요. 아래의 전체 설정을 schema.sql로 저장하세요. 일반적인 비분할 테이블을 사용하며 확장 기능은 필요하지 않습니다.

BEGIN;

CREATE TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TABLE article_bundles (
    bundle_id integer PRIMARY KEY,
    label text NOT NULL
);

CREATE TABLE bundle_refs (
    bundle_id integer NOT NULL
        REFERENCES article_bundles (bundle_id),
    slot text NOT NULL
        CHECK (slot IN ('primary', 'secondary')),
    article_id integer NOT NULL
        REFERENCES articles (article_id),
    PRIMARY KEY (bundle_id, slot),
    CONSTRAINT bundle_refs_article_once UNIQUE (article_id)
);

INSERT INTO articles (article_id, title) VALUES
    (101, 'Grid spacing'),
    (102, 'Type scale'),
    (103, 'Page margins'),
    (104, 'Caption layout'),
    (105, 'Colour palette');

INSERT INTO article_bundles (bundle_id, label)
VALUES (1, 'Opening notes');

INSERT INTO bundle_refs (bundle_id, slot, article_id) VALUES
    (1, 'primary', 101),
    (1, 'secondary', 102);

COMMIT;

macOS Terminal에서 PGDATABASE에 기존 예제 데이터베이스 이름을 지정한 다음 파일을 실행하세요. 이 명령은 로컬 연결 설정이 이미 작동한다고 가정합니다.

export PGDATABASE=article_demo
psql -v ON_ERROR_STOP=1 -f schema.sql

이 설계에서는 묶음에 참조가 0개, 1개 또는 2개 있을 수 있습니다. 고유성을 적용하고 역할을 최대 두 개로 제한하지만 모든 묶음에 두 역할이 모두 있어야 하는 것은 아닙니다. 이는 별도의 요구 사항입니다. 참조가 없으면 해당 행도 없습니다.

읽을 때는 두 열 형태 유지하기

애플리케이션에서 묶음마다 행 하나와 참조 필드 두 개를 가져올 수 있습니다. 설정 후 다음 쿼리를 실행하세요.

SELECT b.bundle_id,
       b.label,
       p.article_id AS primary_article_id,
       s.article_id AS secondary_article_id
FROM article_bundles AS b
LEFT JOIN bundle_refs AS p
  ON p.bundle_id = b.bundle_id AND p.slot = 'primary'
LEFT JOIN bundle_refs AS s
  ON s.bundle_id = b.bundle_id AND s.slot = 'secondary'
ORDER BY b.bundle_id;

초기 결과에는 참조 101과 102가 있는 묶음 1이 표시됩니다. 스키마의 기본 키는 각 조인에서 해당 슬롯의 참조가 최대 하나만 검색되도록 보장합니다. 참조 값을 두 번째로 저장하지 말고 이 쿼리를 애플리케이션의 읽기 형태로 사용하세요.

node-postgres로 두 참조를 어떻게 삽입하나요?

묶음과 참조를 하나의 트랜잭션에서 생성하세요. 참조 충돌이 하나라도 발생하면 묶음도 롤백해야 합니다. node-postgres에서는 트랜잭션의 모든 문이 동일한 대여 클라이언트를 사용해야 하므로, 이 작업에는 pool.query()를 별도로 호출하는 방식이 적합하지 않습니다. 트랜잭션

로컬 프로젝트 디렉터리에서 문서화된 pg 패키지를 설치하세요. 최신 Node.js LTS 릴리스를 사용하고 아래 프로그램을 demo.mjs로 저장하세요. SQL 설정을 실행한 다음 동일한 연결 환경에서 이 프로그램을 실행하세요. node-postgres 문서에서 설치와 환경 변수 기반 연결을 확인할 수 있습니다.

npm install pg
node demo.mjs

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool();

async function addBundle(bundleId, label, primaryId, secondaryId) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'INSERT INTO article_bundles (bundle_id, label) VALUES ($1, $2)',
      [bundleId, label]
    );

    await client.query(
      `INSERT INTO bundle_refs (bundle_id, slot, article_id)
       VALUES ($1, 'primary', $2), ($1, 'secondary', $3)`,
      [bundleId, primaryId, secondaryId]
    );

    await client.query('COMMIT');
    console.log(`Added bundle ${bundleId}`);
  } catch (error) {
    await client.query('ROLLBACK');

    if (
      error.code === '23505' &&
      error.constraint === 'bundle_refs_article_once'
    ) {
      console.log(
        `Bundle ${bundleId} rejected: an article is already referenced`
      );
      return;
    }

    throw error;
  } finally {
    client.release();
  }
}

try {
  await addBundle(2, 'Layout notes', 103, 104);
  await addBundle(3, 'Colour notes', 105, 101);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

이 프로그램은 node-postgres의 쿼리 API에 따라 번호가 붙은 매개변수와 별도의 값 배열을 전달합니다. 첫 실행 시 예상되는 출력은 다음과 같습니다.

Added bundle 2
Bundle 3 rejected: an article is already referenced

두 번째 트랜잭션은 문서 101을 다시 사용하려고 합니다. 롤백으로 묶음 3이 제거되고 문서 105는 참조되지 않은 상태로 남습니다. 이름이 지정된 고유성 충돌에만 중복 메시지가 표시되며, 다른 오류는 그대로 전달됩니다. PostgreSQL은 고유성 위반을 SQLSTATE 23505로 식별하고 제약 조건 이름을 메시지 텍스트와 별도로 제공합니다. PostgreSQL 오류 코드

앞서 나온 읽기 쿼리를 다시 실행해 결과를 확인하세요. 참조 101–104가 있는 묶음 1과 2가 남아 있어야 합니다. 이 예시는 설정을 새로 한 경우 한 번만 실행하세요. 프로그램을 다시 실행하면 기존 묶음 2 기본 키와 충돌합니다.

COALESCE에 대한 고유 인덱스로 충분한 경우는 언제인가요?

행에 실제 참조가 최대 하나만 들어가는 경우에만 이 더 간단한 설계를 사용하세요. COALESCE은 첫 번째 null이 아닌 인수를 반환하므로, 첫 번째 열에 값이 있으면 인덱싱된 표현식에서 두 번째 열의 값이 가려집니다. PostgreSQL에서는 인수들이 공통 타입으로 해석되어야 합니다. 조건식

예를 들어 행 (101, 102)와 (103, 101)는 표현식 값 101과 103을 생성합니다. 이 표현식 값에 대한 고유 인덱스는 문서 101이 두 번 나타나는데도 두 행을 허용합니다. 이 반례는 표현식에 행 형태 제약이 필요한 이유를 보여 줍니다.

정확히 하나만 허용하는 규칙을 적용하려면 앞서 나온 articles 카탈로그를 만든 뒤 다음 대체 예제를 실행하세요. 별도의 테이블을 사용해 대체 스키마를 보여 주며, bundle_refs에 참조를 추가하지 않습니다.

CREATE TABLE single_article_refs (
    reference_id integer PRIMARY KEY,
    primary_article_id integer REFERENCES articles (article_id),
    secondary_article_id integer REFERENCES articles (article_id),
    CONSTRAINT exactly_one_article CHECK (
        (primary_article_id IS NULL)
        <> (secondary_article_id IS NULL)
    )
);

CREATE UNIQUE INDEX single_article_refs_article_once
ON single_article_refs (
    (COALESCE(primary_article_id, secondary_article_id))
);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (1, 101, NULL);

INSERT INTO single_article_refs
    (reference_id, primary_article_id, secondary_article_id)
VALUES (2, NULL, 101)
ON CONFLICT (
    (COALESCE(primary_article_id, secondary_article_id))
)
DO NOTHING
RETURNING reference_id;

검사는 null이 아닌 두 불리언 결과를 비교해 한 열은 null이고 다른 열에는 값이 있는 경우만 허용합니다. PostgreSQL은 같은 행의 여러 열을 대상으로 하는 검사를 지원합니다. PostgreSQL 14 제약 조건

고유 표현식 인덱스는 값이 있는 어느 열이든 하나의 비교 영역에 넣습니다. 표현식 인덱스는 최신 PostgreSQL과 PostgreSQL 14 문서에 설명되어 있습니다. 이 방법에는 PostgreSQL 15에 추가된 null 처리 구문이 필요하지 않습니다.

두 번째 삽입은 문서 101이 이미 참조되고 있으므로 아무 행도 반환하지 않습니다. 표현식을 ON CONFLICT 대상으로 사용할 수 있으며, RETURNING은 실제로 삽입되거나 업데이트된 행을 보고합니다. 결과가 비어 있으면 삽입이 건너뛰어진 것으로 처리하세요. 표현식 인덱스 이름은 ON CONFLICT ON CONSTRAINT에 사용할 테이블 제약 조건 이름이 아닙니다. INSERT

두 열이 모두 비어 있어도 된다면 테이블을 정의할 때 정확히 하나를 검사하는 조건을 다음 행 수준 조건으로 바꾸세요. 표현식 인덱스는 그대로 유지하세요. 그러면 PostgreSQL의 기본 고유 인덱스 null 처리 방식에 따라 모든 열이 null인 행을 여러 개 허용합니다. 고유 인덱스

-- Alternative CHECK fragment for the CREATE TABLE definition:
CHECK (
    primary_article_id IS NULL
    OR secondary_article_id IS NULL
)

기존 테이블을 변경하기 전에 중복을 어떻게 진단하나요?

UNION ALL로 두 열을 하나의 스트림으로 펼친 다음 문서 ID별로 그룹화하세요. 중복을 그대로 유지해야 합니다. 일반 UNION은 찾아야 할 반복의 증거를 제거할 수 있습니다. 이 전체 진단 쿼리는 임의로 만든 입력을 사용하며 위의 스키마와 독립적으로 실행됩니다.

WITH legacy_refs (bundle_id, primary_article_id, secondary_article_id) AS (
    VALUES
        (1, 101, 102),
        (2, 103, 101),
        (3, 104, 104)
),
expanded AS (
    SELECT bundle_id, 'primary' AS slot,
           primary_article_id AS article_id
    FROM legacy_refs
    WHERE primary_article_id IS NOT NULL

    UNION ALL

    SELECT bundle_id, 'secondary' AS slot,
           secondary_article_id AS article_id
    FROM legacy_refs
    WHERE secondary_article_id IS NOT NULL
)
SELECT article_id,
       count(*) AS occurrences,
       string_agg(
           bundle_id::text || ':' || slot,
           ', ' ORDER BY bundle_id, slot
       ) AS locations
FROM expanded
GROUP BY article_id
HAVING count(*) > 1
ORDER BY article_id;

예상되는 중복 ID는 101과 104이며, 각각 두 번 나타납니다. 기존 테이블에서 실행하려면 첫 번째 CTE를 실제 테이블의 열로 바꾸세요. 마이그레이션 전에 보고된 각 항목을 검토하세요. 행을 자동으로 제거하면 의도된 참조가 사라질 수 있습니다. 모든 참조를 선택한 표현 방식으로 옮기고 이후 쓰기는 그곳으로 보내세요. 예시의 대체 테이블은 고유성 적용을 공유하지 않기 때문입니다.

최종 결정을 데이터베이스에서 적용해야 하는 이유는 무엇인가요?

사전 조회는 기존 충돌을 설명할 수 있지만, 그 자체로 고유성을 보장하지는 못합니다. 두 개의 동시 작업이 어느 쪽도 삽입하기 전에 사용되지 않은 참조라고 판단할 수 있습니다. PostgreSQL의 고유성 구현은 인덱스 삽입 과정에서 충돌을 확인하고, 필요한 경우 충돌 중인 미커밋 트랜잭션이 끝날 때까지 기다린 뒤 결과를 다시 확인합니다. 인덱스 고유성 검사

다른 행을 조회하는 CHECK로 이 기능을 대체하지 마세요. PostgreSQL은 여러 행을 넘나드는 검사를 지속적으로 유지되는 보장으로 지원하지 않는다고 명시합니다. 허용할 열 형태에는 행 검사를 사용하고 반복 참조에는 고유 제약 조건이나 인덱스를 사용하세요. 제약 조건

JavaScript 프로그램은 구문 검사를 통과했고 반례 및 정확히 하나를 허용하는 진리표는 결정론적으로 확인했습니다. PostgreSQL 예시는 문서를 기준으로 검토했지만 이 연구 환경에서는 데이터베이스에서 실행하지 않았습니다.

참조 위치가 두 개 모두 채워질 수 있다면 참조 테이블 설계로 단일 고유 문서 ID를 통해 규칙을 직접 적용할 수 있습니다. 열 중 하나만 선택할 수 있다면 검사가 있는 COALESCE 설계로 더 간단한 스키마를 유지할 수 있습니다. 허용할 행 형태에 맞춰 선택한 다음, 데이터베이스가 최종 고유성 여부를 결정하도록 하세요.

출처