UNIQUE인데 NULL이 반복되는 상황

배송비 규칙에 country_code = NULL인 행을 전체 국가에 적용하는 기본 규칙으로 사용한다고 가정합니다. UNIQUE (shop_id, country_code)를 선언해도 같은 shop에 기본 규칙이 여러 개 들어갈 수 있습니다. PostgreSQL에서 일반 UNIQUE constraint는 NULL을 서로 같은 값으로 비교하지 않기 때문입니다.

PostgreSQL의 UNIQUE constraint는 기본적으로 NULL을 서로 distinct하게 취급해 여러 NULL을 허용하며, PostgreSQL 15 이상에서는 UNIQUE NULLS NOT DISTINCT로 NULL도 같은 값처럼 제한할 수 있습니다.

business 의미에 맞는 constraint 선택

NULL이 단순히 ‘아직 입력하지 않음’을 뜻한다면 여러 NULL을 허용하는 기본 동작이 맞을 수 있습니다. 반대로 NULL이 ‘이 shop의 기본 규칙’이라는 하나의 상태를 뜻한다면 PostgreSQL 15 이상에서 NULLS NOT DISTINCT를 사용해 같은 shop의 NULL 조합도 중복으로 판단할 수 있습니다.

CREATE TABLE shipping_rules (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  shop_id bigint NOT NULL,
  country_code text,
  fee numeric(10, 2) NOT NULL,
  CONSTRAINT uq_shipping_rule
    UNIQUE NULLS NOT DISTINCT (shop_id, country_code)
);

INSERT INTO shipping_rules (shop_id, country_code, fee)
VALUES (10, NULL, 3000);

-- 같은 shop의 두 번째 기본 규칙: SQLSTATE 23505
INSERT INTO shipping_rules (shop_id, country_code, fee)
VALUES (10, NULL, 2500);

-- shop이 다르므로 허용
INSERT INTO shipping_rules (shop_id, country_code, fee)
VALUES (20, NULL, 2500);

여러 column을 묶은 unique constraint는 모든 key 값의 조합으로 판정합니다. 따라서 shop_id가 다르면 country_code가 모두 NULL이어도 충돌하지 않습니다.

기존 table에 적용하기 전 확인

constraint를 추가하기 전에 같은 기준으로 이미 존재하는 중복을 찾고 어떤 행을 보존할지 결정합니다. PostgreSQL 14 이하에는 이 문법이 없으므로 version을 먼저 확인합니다. 또한 UNIQUE constraint를 만들면 PostgreSQL이 unique B-tree index를 자동 생성하므로 같은 column에 성능용 index를 다시 만들 필요는 없습니다.

SELECT shop_id, country_code, count(*)
FROM shipping_rules
GROUP BY shop_id, country_code
HAVING count(*) > 1;

정상 값 중복, 같은 shop의 NULL 중복, 다른 shop의 NULL, concurrent insert를 각각 transaction test로 검증합니다. 다른 DB 제품은 unique constraint의 NULL 취급과 지원 문법이 다를 수 있으므로 이 결론을 그대로 옮기지 않습니다.

공식 문서

현재 운영 PostgreSQL major version과 실제 NULL의 business 의미를 기준으로 적용합니다.