답변.

관계형 데이터베이스에서 고유성을 보장하는 작업은 종종 UNIQUE 제약 조건에 의해 수행됩니다. 그러나 비즈니스 관행에서 일부 필드가 NULL일 수 있는 조합에 따라 고유성이 요구되는 상황이 발생할 수 있습니다(예: 이메일과 전화번호의 고유한 조합, 하지만 전화번호는 알 수 없음).

문제의 역사: SQL의 기본 UNIQUE 제약 조건은 NULL인 열이 있는 경우 고유성을 보장하지 않습니다. 스펙에서는 이러한 값들이 상호 배타적이라고 간주합니다.

문제: NULL이 포함된 복합 키의 경우 기본 제약 조건이 중복을 발생시킵니다. 이는 데이터 무결성이 필요할 때 특히 중요합니다: 데이터 가져오기, 마이그레이션, 대량 업데이트 시.

해결책: 모든 값을 고려하는 계산된 열을 사용하고(여기에는 NULL도 포함됨) 그러한 열에 고유성을 적용하거나 트리거 로직을 사용할 수 있습니다.

코드 예시:

ALTER TABLE my_table
ADD computed_uniqueness AS (
   ISNULL(email, '') + '#' + ISNULL(phone, '')
);   
CREATE UNIQUE INDEX idx_my_table_computed_uniqueness ON my_table(computed_uniqueness);

또는 (NULL을 다르게 취급하는 PostgreSQL의 경우):

CREATE UNIQUE INDEX idx_unique_email_phone ON my_table ((COALESCE(email, '##')),(COALESCE(phone, '##')));

주요 특징:

NULL이 포함된 조합에 대한 고유성 규칙의 명시적 관리.
데이터베이스 수준에서 자동화 및 지원 가능.
복잡하고 변경 가능한 스키마에 대한 지원.

속임수의 질문.

NULL을 허용하는 일부 열이 있는 경우 일반 UNIQUE 인덱스를 사용하여 고유성을 보장할 수 있습니까?

아니요. ANSI SQL에 따르면 UNIQUE 인덱스는 조합에서 적어도 하나의 열이 NULL인 여러 행을 허용하므로 NULL은 다른 어떤 값과도 같지 않게 간주됩니다.

표현식에 대한 고유 인덱스 사용과 고유성을 확인하기 위한 BEFORE INSERT 트리거 사용 사이의 차이점은 무엇인가요?

고유 인덱스는 유지 관리가 더 간단하고 작업 속도가 빠르지만 항상 복잡한 비즈니스 규칙(예: 예외 또는 사용자 정의 조합)을 구현할 수는 없습니다. 트리거는 더 유연하지만 느리고 유지 관리가 더 복잡합니다.

예시:

CREATE OR REPLACE FUNCTION check_custom_unique()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM my_table
    WHERE COALESCE(NEW.email, '##') = COALESCE(email, '##')
      AND COALESCE(NEW.phone, '##') = COALESCE(phone, '##')
  ) THEN
    RAISE EXCEPTION 'Duplicate found';
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_custom_unique
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION check_custom_unique();

응용 프로그램 레벨에서 고유성 문제를 해결하기 위해 SELECT에서 DISTINCT를 사용할 수 있습니까?

가능하지만 선택에만 해당됩니다 — 이는 데이터 수정 중 중복 생성을 방지하지 않으며 테이블 수준의 제약을 대체하지 않습니다.

일반적인 오류 및 안티 패턴

NULL이 포함된 조합으로 키가 설정된 경우 UNIQUE 제약 조건을 사용하려는 시도.
데이터베이스를 우회하여 애플리케이션 수준에서만 고유성을 확인하려는 시도.
명시적 인덱스가 없는 트리거의 과도한 복잡성 — 성능 저하.

실제 사례

부정적인 케이스

회사가 UNIQUE(email, phone)을 통해 email+전화번호의 고유성을 도입합니다. 전화번호가 NULL일 경우 데이터베이스에 email의 중복이 발생합니다.

장점:

구현의 용이성.

단점:

무결성 손실, 중복 발생.
분석에서 추적하기 어려운 암묵적인 오류.

긍정적인 케이스

계산된 열(COALESCE(email, '##') + '#' + COALESCE(phone, ''))를 사용하고 그 위에 고유 인덱스를 설정합니다.