null 열을 사용하여 고유한 제약 조건을 생성합니다.

null 열을 사용하여 고유한 제약 조건을 생성합니다.

다음 레이아웃의 테이블이 있습니다.

CREATE TABLE Favorites (
  FavoriteId uuid NOT NULL PRIMARY KEY,
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  MenuId uuid
);

다음과 유사한 고유한 제약 조건을 생성하려고 합니다.

ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

그러나 이렇게 하면 같은 행이 여러 개 허용됩니다.(UserId, RecipeId) if ,면,입니다.MenuId IS NULL허락하고 싶어요NULL에 있습니다.MenuId연결된 메뉴가 없는 즐겨찾기를 저장할 수 있지만, 사용자/레시피 쌍당 이러한 행 중 하나만 사용할 수 있습니다.

지금까지의 아이디어는 다음과 같습니다.

1. null.null UUID(예: 0) 대신 하드 된 UUID를 사용합니다.
   하지만, 만,,는요,MenuId각 사용자의 메뉴에 FK 제약이 있기 때문에 모든 사용자를 위해 특별한 "null" 메뉴를 만들어야 하므로 번거롭습니다.

2. 대신 트리거를 사용하여 null 항목이 있는지 확인하십시오.
   저는 이것이 번거롭다고 생각하며 가능한 한 방아쇠를 당기는 것을 좋아합니다.또한 제 데이터가 절대로 나쁜 상태가 되지 않는다고 보장할 수 없습니다.

3. 잊고 미들웨어 또는 삽입 함수에 null 항목이 이전에 존재하는지 확인하기만 하면 됩니다. 그리고 이러한 제약은 없습니다.

Postgres 9.0을 사용하고 있습니다.제가 간과하고 있는 방법이 있나요?

포스트그레스 15 이상입니다.

Postgres 15는 절을 추가합니다.릴리스 노트는 다음과 같습니다.

• 고유한 제약 조건 및 인덱스가 NULL 값을 구별되지 않는 것으로 처리하도록 허용합니다(Peter Eisentraut).

  에는 NULL 값이 항상 고유한 값으로 인덱싱되었지만, 이제는 NULL 값을 사용하여 제약 조건 및 인덱스를 생성하여 변경할 수 있습니다.UNIQUE NULLS NOT DISTINCT요.

이 절과 함께요.null다른 값처럼 처리되며 제약조건은 동일한 행을 두 개 이상 허용하지 않습니다.null. 작업은 단순합니다. now:value. 이겁니다.

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

설명서 "고유 구속조건" 장에 예가 있습니다.
절은 동일한 인덱스의 모든 키에 대한 동작을 전환합니다.치료할 수 없습니다.null한 키에는 동일하지만 다른 키에는 동일하지 않습니다.
NULLS DISTINCT표준 SQL과 일치하는 기본값이 유지되므로 철자를 입력할 필요가 없습니다.

인덱스에도 동일한 절이 적용됩니다.

CREATE UNIQUE INDEX favo_uni_idx
ON favorites (user_id, menu_id, recipe_id) NULLS NOT DISTINCT;

키 필드 뒤에 있는 새 절의 위치를 확인합니다.

우편번호 14 이상입니다.

두 개의 부분 인덱스를 만듭니다.

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;

이렇게 하면 '비슷하다'의 조합은 딱 한 가지밖에 없을 수 있습니다.(user_id, recipe_id)여기서, where where에 wheremenu_id IS NULL원하는 제약 조건을 효과적으로 구현합니다.

가능한 단점은 다음과 같습니다.

• 외부 키를 사용할 수 없습니다.(user_id, menu_id, recipe_id) (FK 참조의 너비가 3개인 경우는 거의 없을 것 같습니다. 대신 PK 열을 사용하십시오!) (FK 참조의 너비가 3열인 경우는 거의 없을 것 같습니다. PK입니다!
• 기본이 될 수 없습니다.CLUSTER이겁니다.
• 일치하지 않는 입니다.WHERE조건은 부분 인덱스를 사용할 수 없습니다.

전체 인덱스가 필요한 경우 대신 인덱스를 삭제할 수 있습니다.WHERE부 condition condition condition from from from from from from from 。favo_3col_uni_idx이겁니다.
이제 전체 테이블로 구성된 인덱스가 다른 테이블과 겹치며 더 커집니다.일반적인 질의와 비율에 따라 달라집니다.null이겁니다.극단적인 상황에서는 세 가지 인덱스(부분적인 인덱스 두 개 및 맨 위에 있는 전체 인덱스)를 모두 유지하는 데 도움이 될 수도 있습니다.

이 솔루션은 단일 null 가능 열, 또는 두 열에 대해 적합한 솔루션입니다.그러나 nullable 열의 모든 조합에 대해 별도의 부분 인덱스가 필요하기 때문에 이 값은 이원적으로 증가합니다.다중 Null 가능 열은 대신 다음을 참조하십시오.

• 고유 제약 조건이 트리거되지 않는 이유는 무엇입니까?

여하튼: Postgre에서 대/소문자 혼합 식별자를 사용하지 않는 것이 좋습니다.SQL입니다.

MenuId에서 병합을 사용하여 고유한 인덱스를 생성할 수 있습니다.

CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);

"실제"에서는 절대 발생하지 않는 COALESCE의 UUID를 선택하면 됩니다.실제로 UUID가 0인 경우는 거의 볼 수 없겠지만, 편집증적이라면 CHECK 제약 조건을 추가할 수 있습니다.):

alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')

연결된 메뉴가 없는 즐겨찾기를 별도의 테이블에 저장할 수 있습니다.

CREATE TABLE FavoriteWithoutMenu
(
  FavoriteWithoutMenuId uuid NOT NULL, --Primary key
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  UNIQUE KEY (UserId, RecipeId)
)

이전 답변을 보다 최적의 솔루션으로 조합하는 옵션이 있다고 생각합니다.

create table unique_with_nulls (
    id serial not null,
    name varchar not null,
    age int2 not null,
    email varchar,
    email_discriminator varchar not null generated always as ( coalesce(email::varchar, 0::varchar) ) stored,

    constraint uwn_pkey primary key (id)
);

create unique index uwn_name_age_email_uidx on unique_with_nulls(name, age, email_discriminator);

여기서 일어나는 일은 열입니다.email_discriminator실제 전자 메일인 "삽입 또는 업데이트 시간"에 생성되거나, 이전 전자 메일이 null인 경우 "0"에 생성됩니다.그런 다음 고유 지수가 판별자 열을 대상으로 해야 합니다.
이렇게 두 개의 부분 인덱스를 만들 가 없고, 색인화된 스캔을 사용할 수 있는 기능이 느슨해지지 않습니다.name그리고요.age이겁니다.
그리고 그 종류도 할 수 있어요.email열과 우리는 합체 함수에 가 없습니다. 왜냐하면 합체 함수는 문제가 없기 때문입니다.email_discriminator이겁니다.또한 생성된 열을 쓸 수 없으므로 이 열이 예기치 않은 값을 받을까 걱정할 필요가 없습니다.

이 솔루션에서 세 가지 단점을 볼 수 있지만, 이러한 단점은 모두 제 요구 사항에 부합합니다.

• 의 중복을 말합니다.email그리고요.email_discriminator요.
• 칼럼에 쓰고 다른 칼럼에서 읽어야 한다는 사실을요
• 가능한 값의 집합을 벗어나는 값을 찾을 입니다.email대체물이 될 수 있습니다(때로는 찾기 어렵거나 주관적일 수도 있습니다).

의미론적인 문제가 있는 것 같습니다.제가 보기에, 사용자는 특정 메뉴를 준비하기 위해 좋아하는 레시피를 가질 수 있습니다. (OP에 메뉴와 레시피가 뒤섞여 있습니다. 제가 틀렸다면 아래의 MenuId와 RecipeId를 바꿔주세요.)즉, {user,menu}은(는) 이 테이블의 고유 키여야 합니다.그리고 정확히 하나의 레시피를 가리켜야 합니다.사용자가 이 특정 메뉴에 대해 즐겨찾는 레시피가 없는 경우 이 {user,menu} 키 쌍에 대한 행이 없어야 합니다.또한 대리 키(FaVouRiteId)가 필요 없습니다. 복합 기본 키는 관계형 매핑 테이블에 완벽하게 유효합니다.

그러면 테이블 정의가 줄어듭니다.

CREATE TABLE Favorites
( UserId uuid NOT NULL REFERENCES users(id)
, MenuId uuid NOT NULL REFERENCES menus(id)
, RecipeId uuid NOT NULL REFERENCES recipes(id)
, PRIMARY KEY (UserId, MenuId)
);

언급URL : https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns 입니다.