[SQL] 널 열 고유 제한 조건을 만들기

널 열 고유 제한 조건을 만들기

나는이 레이아웃 테이블이 :

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);

menuId와이 NULL 인 경우에는, 이것은 동일한 (사용자 아이디, RecipeId)과 복수의 열을 허용 할 것이다. 나는 어떤 메뉴를 관련 지을 수있는 즐겨 찾기를 저장하는 menuId와의 NULL을 허용 할,하지만 난 단지 사용자 / 조리법 쌍 당이 행의 대부분 하나를 원한다.

내가 지금까지 가지고있는 아이디어가 있습니다 :

나는 포스트 그레스 9.0을 사용하고 있습니다.

내가 바라 보는거야 어떤 방법이 있습니까?

해결법

1. ==============================

   1.이 부분 인덱스를 생성합니다 :

   이 부분 인덱스를 생성합니다 :

   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이고 이렇게 만 (USER_ID,에는 Recipe_ID) 중 하나의 조합이있을 수있다.

   가능한 단점 : 당신이 외래 키 참조를 (USER_ID, menu_id,에는 Recipe_ID) 가질 수 없습니다, 당신은 조건이 부분 인덱스를 사용할 수 없습니다 일치하지하지베이스 부분 인덱스에 CLUSTER, 및 쿼리 할 수 ​​있습니다. (- 대신 PK 컬럼을 사용 당신이 세 개의 열 폭 FK 참조를 원하는 것 같지는 않다).

   당신이 전체 색인이 필요한 경우 선택적으로 favo_3col_uni_idx에서 WHERE 조건을 삭제할 수 있습니다 귀하의 요구 사항은 여전히 ​​적용됩니다. 지수는 현재 전체 테이블을 포함, 다른 하나 겹쳐 더 큰 가져옵니다. 일반적인 쿼리와 NULL 값의 비율에 따라,이 또는 유용하지 않을 수도 있습니다. 극단적 인 상황에서 심지어 세 인덱스 (두 부분들과 위에 총)을 유지하는 데 도움이 될 수 있습니다.

   제외 : 나는 PostgreSQL을에 혼합 된 경우 식별자를 사용하지 않는 것이 좋습니다.

2. ==============================

   2.당신은 menuId와의 유착과 고유 인덱스를 만들 수 있습니다 :

   당신은 menuId와의 유착과 고유 인덱스를 만들 수 있습니다 :

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

   당신은 단지 "현실"에서 발생하지 않을 것 COALESCE에 대한 UUID를 선택해야 할 것입니다. 당신은 아마 현실에서 제로 UUID를 볼 수 없을 것입니다하지만 당신은 당신이 편집증 경우 CHECK 제약 조건을 추가 할 수 있습니다 (그들이 정말로 때문에 ... 당신을 얻기 위해) :

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

3. ==============================

   3.별도의 테이블에 관련된 메뉴와 즐겨 찾기를 저장할 수 있습니다 :

   별도의 테이블에 관련된 메뉴와 즐겨 찾기를 저장할 수 있습니다 :

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

4. ==============================

   4.나는 의미 론적 문제가 여기에 있다고 생각합니다. 내 관점에서, 사용자는 특정 메뉴를 준비하는 (그러나 단 하나의) 마음에 드는 조리법을 가질 수 있습니다. (영업 이익은 메뉴와 조리법 혼합을 가지고, 만약 내가 잘못 : 제발 아래 menuId와 및 RecipeId 교환) 즉 {사용자 메뉴}이 테이블의 고유 키해야한다는 것을 의미한다. 그리고 그것은 정확히 하나 개의 레시피를 가리켜 야합니다. 사용자는 더 행이 {사용자 메뉴} 키 쌍에 대해 존재하지 않는해야이 특정 메뉴에 대한 마음에 드는 조리법이없는 경우. 또한 : 대리 키 (FaVouRiteId)는 불필요 : 복합 기본 키 관계형 매핑 테이블에 완벽하게 유효합니다.

   나는 의미 론적 문제가 여기에 있다고 생각합니다. 내 관점에서, 사용자는 특정 메뉴를 준비하는 (그러나 단 하나의) 마음에 드는 조리법을 가질 수 있습니다. (영업 이익은 메뉴와 조리법 혼합을 가지고, 만약 내가 잘못 : 제발 아래 menuId와 및 RecipeId 교환) 즉 {사용자 메뉴}이 테이블의 고유 키해야한다는 것을 의미한다. 그리고 그것은 정확히 하나 개의 레시피를 가리켜 야합니다. 사용자는 더 행이 {사용자 메뉴} 키 쌍에 대해 존재하지 않는해야이 특정 메뉴에 대한 마음에 드는 조리법이없는 경우. 또한 : 대리 키 (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)
   );
   

from https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns by cc-by-sa and MIT license