NULL 열이 있는 PostgreSQL에서 고유 제약 조건을 적용하는 방법은 무엇입니까?

PostgreSQL 고유 제약 조건 및 NULL 열: 실용 가이드

Null 허용 열을 처리할 때 PostgreSQL에서 고유 제약 조건을 처리하려면 신중하게 고려해야 합니다. UserId, MenuId 및 RecipeId 열이 있고 UserId 및 RecipeId이 null이 아닌 테이블이 있다고 상상해 보세요. 이러한 열에 대한 표준 고유 제약 조건은 NULL 값을 포함하여 MenuId이 다른 경우 중복 항목을 허용합니다.

이 문제를 해결하기 위한 효과적인 전략은 다음과 같습니다.

PostgreSQL 15 이상 버전

PostgreSQL 15에서는 NULLS NOT DISTINCT 절을 도입하여 간단한 솔루션을 제공했습니다. 이 절은 제약 조건과 인덱스 내에서 고유성을 적용할 때 NULL 값을 동일하게 처리합니다.

ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);

PostgreSQL 14 및 이전 버전

이전 PostgreSQL 버전의 경우 권장되는 접근 방식은 부분 인덱스를 생성하는 것입니다.

CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;

menu_id이 NULL이고 NULL이 아닌 행에 대해 별도의 인덱스를 생성하여 고유성을 효과적으로 적용합니다.

부분 인덱스에 대한 중요 고려 사항

부분 인덱스를 사용하면 몇 가지 제한 사항이 발생합니다.

• 3개 열 조합(user_id, menu_id, recipe_id)에는 외래 키 제약 조건을 직접 적용할 수 없습니다.
• 부분 인덱스 기반 클러스터링은 지원되지 않습니다.
• 일치하는 WHERE 절이 없는 쿼리는 부분 인덱스를 활용하지 않습니다.

모범 사례:

일관성을 유지하고 잠재적인 문제를 방지하려면 PostgreSQL에서 소문자 식별자(예: favorites)를 사용하는 것이 좋습니다. 적절한 방법을 선택하는 것은 PostgreSQL 버전과 특정 요구 사항에 따라 다릅니다. NULLS NOT DISTINCT 절은 최신 버전에서 더 깔끔한 솔루션을 제공하는 반면, 부분 인덱스는 이전 버전에 대한 기능적 대안을 제공합니다.

위 내용은 NULL 열이 있는 PostgreSQL에서 고유 제약 조건을 적용하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!