PostgreSQL UNIQUE에 NULL 두 개를 넣으면 어떻게 될까

PostgreSQL 18의 기본 UNIQUE는 고유성 검사에서 NULL끼리를 같은 값으로 보지 않으므로 여러 NULL을 허용합니다. UNIQUE NULLS NOT DISTINCT는 그 검사에서 NULL을 같은 값으로 취급합니다.

이는 WHERE에서 NULL을 비교하는 연산 규칙과 별개의 제약 조건입니다. 'NULL이 허용되는가'와 'NULL을 가진 행이 여러 개여도 되는가'를 두 질문으로 나누어 보세요.

내선 번호를 아직 받지 않은 직원 두 명

직원 번호 employee_id는 각 행을 구분하고, extension에는 배정된 내선 번호를 기록한다고 하겠습니다. 아직 내선을 받지 않은 경우 NULL을 넣는 창작 사례입니다. 여기서 0이나 빈 문자열을 '없음'의 대용으로 섞지 않습니다. 번호가 없다는 상태와 실제 내선 번호 0은 서로 다른 자료 표현이기 때문입니다.

CREATE TABLE employee (
  employee_id integer PRIMARY KEY,
  extension integer UNIQUE
);
INSERT INTO employee VALUES (1, NULL);
INSERT INTO employee VALUES (2, NULL);
INSERT INTO employee VALUES (3, 410);

기본 설정에서는 첫 번째와 두 번째 직원 모두 저장할 수 있습니다. 세 번째 직원의 410도 다른 배정 번호와 겹치지 않아 허용됩니다. 그다음 (4,410)을 넣으면 이미 410이 있으므로 고유 제약을 위반합니다. 이때 employee_id가 4로 다르다는 사실은 extension의 중복을 없애 주지 않습니다. 두 열에 각각 걸린 제약을 따로 판단해야 합니다.

NULLS NOT DISTINCT로 바꾸면

CREATE TABLE employee_limited (
  employee_id integer PRIMARY KEY,
  extension integer UNIQUE NULLS NOT DISTINCT
);
INSERT INTO employee_limited VALUES (1, NULL);
-- 별도 문장으로 실행하면 아래 INSERT는 거부됩니다.
INSERT INTO employee_limited VALUES (2, NULL);

NULLS NOT DISTINCT는 'NULL 금지'가 아닙니다. 첫 NULL이 들어가고 두 번째가 중복으로 거부된다는 것이 핵심입니다. 값 자체를 반드시 받으려면 NOT NULL이라는 별도 조건이 필요합니다. 예제의 목적은 동작 비교이며, 실제 직원 데이터에 '미배정은 한 명만 가능'한 규칙이 맞는지는 업무 요구에 따라 판단해야 합니다.

실험할 때 여러 INSERT를 하나의 명시적 트랜잭션으로 묶고 오류를 낸 뒤 계속 진행하면 트랜잭션 오류 처리까지 섞일 수 있습니다. 위 표는 각 제약을 별도 빈 테이블에 두고 삽입 허용 여부를 비교한 표입니다. 복습 첫 단계에서는 오류가 발생하는 문장만 찾고, 저장된 최종 상태는 실행 단위까지 확인해서 적어 보세요.

지점이 다르면 NULL 두 개도 가능한가

이제 내선 번호를 지점별로 관리하려고 (branch_id, extension) 두 열을 하나의 UNIQUE NULLS NOT DISTINCT로 묶습니다. branch_id는 NOT NULL이라고 가정합니다. 비교 단위는 extension 한 칸이 아니라 두 열의 조합 전체입니다. NULL을 같게 취급해도 지점 번호가 다르면 같은 조합이 되지 않습니다.

CREATE TABLE branch_extension (
  employee_id integer PRIMARY KEY,
  branch_id integer NOT NULL,
  extension integer,
  UNIQUE NULLS NOT DISTINCT (branch_id, extension)
);

이 표를 보고 extension만 따로 UNIQUE로 선언하면 안 됩니다. 그러면 지점 10과 20이 같은 내선 410을 쓰는 것도 막는 다른 규칙이 됩니다. 제약 이름만 외우기보다 괄호 안 열 목록에 밑줄을 그으면 문제에서 요구한 중복 범위를 찾기 쉽습니다. 복합 키 한 열이 같다고 전체가 같은 것은 아닙니다.

조건 하나씩 바꾸어 판정하기

PRIMARY KEY는 행의 식별자로 쓰이며 고유성과 NULL 불허를 함께 요구합니다. 하지만 한 테이블에 기본 키가 있다고 다른 열의 고유성이 자동으로 보장되지는 않습니다. PostgreSQL은 UNIQUE나 기본 키를 만들 때 이를 위한 고유 인덱스를 생성하므로 같은 열에 같은 목적의 인덱스를 무조건 한 번 더 만들 필요도 없습니다.

1. 비교할 제약의 열 목록을 적습니다. 단일 열인지 복합 조합인지 먼저 정합니다.
2. NOT NULL이 있는지 확인해 NULL 자체의 허용 여부를 판정합니다.
3. UNIQUE의 NULLS DISTINCT 또는 NULLS NOT DISTINCT 설정을 확인합니다.
4. 후보 행과 기존 행을 제약 열 전체로 대조합니다.
5. 복습 때는 값 하나나 옵션 하나만 바꿔 어떤 판정이 바뀌었는지 설명합니다.

오답 노트에는 'UNIQUE는 NULL을 허용한다' 한 줄 대신 DBMS와 조건을 함께 남겨 보세요. 이 글의 기준은 PostgreSQL 18이며 다른 제품의 기본값까지 확장하지 않습니다. 또한 NULLS NOT DISTINCT를 붙였다고 일반 WHERE의 등호 비교 방식이 바뀌는 것도 아닙니다. 고유성 검사에 적용되는 선택이라는 문맥을 유지해야 합니다.