답변.

질문의 역사

비즈니스 키의 고유성 관리 (ID뿐만 아니라 '자연' 필드에 대해서도)는 기업 애플리케이션에서 오랫동안 계속되어 왔습니다. 이러한 키는 종종 복합적이며 (여러 열로 구성) NULL 값을 허용합니다. 표준 SQL 도구인 UNIQUE 제약 조건이나 고유 인덱스는 제한이 있습니다: SQL 표준에 따르면, UNIQUE 키의 구성 요소 중 하나라도 NULL인 경우 여러 행은 고유한 것으로 간주되어 제약 조건을 위반하지 않습니다.

문제

비즈니스 논리는 열 집합 (NULL을 허용하는)을 전체적으로 고유해야 할 것을 요구할 수 있으며, SQL 표준의 동작 (NULL ≠ NULL)은 이러한 시나리오를 망가뜨립니다. 예를 들어, (passport_number, passport_series)라는 두 개의 열이 있는 테이블이 있을 때, 적어도 하나가 NULL일 수 있지만 값이 같다면 중복을 방지해야 합니다.

해결책

대부분의 인기 있는 데이터베이스 관리 시스템에서 해결책은 트리거를 사용하여 고유성을 확인하거나 조건부 부분 인덱스를 사용하거나, NULL을 동일하게 비교할 수 있는 함수를 사용하는 것입니다 (예: ISNULL 또는 COALESCE). 다음은 PostgreSQL에서 표현식에 대한 고유 인덱스를 사용하는 예입니다:

CREATE UNIQUE INDEX idx_passport_unique
ON persons (
  COALESCE(passport_number, ''),
  COALESCE(passport_series, '')
);

애플리케이션 논리 수준에서 종종 중복 삽입 단계에서 중복을 놓치지 않기 위해 검사를 반복해야 합니다.

주요 특징:

표준 UNIQUE 제약 조건은 키에 하나라도 NULL이 포함되면 작동하지 않습니다.
해결책은 데이터베이스 관리 시스템에 따라 다릅니다: 부분 인덱스, 계산된 열, 트리거.
올바른 인덱스를 생성할 수 없는 경우 비즈니스 논리에 고유성 검사를 맡길 수 있습니다.

기만적인 질문들.

NULL 열이 포함된 경우 고유 인덱스가 중복 삽입을 방지하는 것을 확신할 수 있습니까?

아니요, 확신할 수 없습니다. SQL에서 NULL의 행동은 특별합니다: 적어도 하나가 NULL인 동일한 값 세트를 가진 행은 인덱스에 의해 다르게 간주되어 동시에 저장될 수 있습니다.

다양한 데이터베이스 관리 시스템이 NULL 고유성을 다르게 구현할 수 있습니까?

네, 그들 사이에는 차이가 있습니다. 예를 들어, Oracle의 고유 인덱스는 NULL이 있는 여러 행을 허용하지만, MS SQL은 하나만 허용합니다. PostgreSQL은 표현식을 통해 부분 인덱스를 생성할 수 있습니다.

트리거 없이 DDL 수준에서만 문제를 우회할 수 있습니까?

일부 데이터베이스 관리 시스템에서는 YES, 표현식을 통해 가능하지만 항상 그런 것은 아니며, 복잡한 동작이 필요한 경우 (NULL이 중복으로 간주되어야 하는 경우), 논리적 검사나 트리거를 사용해야 합니다.

일반적인 오류 및 안티 패턴

NULL을 허용하는 열에서 표준 UNIQUE에 의존하기
비즈니스 논리 수준에서 중복 검사를 하지 않기
성능을 고려하지 않고 트리거를 생성하기

실제 사례

부정적인 사례

중요하지 않은 데이터베이스에서는 (email, 부서 코드)에 대한 고유 인덱스에 의존하기로 결정했습니다. 두 필드 모두 NULL을 허용하므로 중복이 여러 번 발생하여 외부 통합이 실패했습니다.

장점:

간단한 DDL

단점:

불명확한 중복, 비일관 데이터, 보고서 오류

긍정적인 사례

PostgreSQL에서 (여권 번호, 시리즈) 필드를 COALESCE에 대한 인덱스를 통해 비즈니스 키로 구현하고, 애플리케이션 쪽에서 추가 검사를 수행했습니다.