[解決済み】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) もし MenuId IS NULL . を許可したい。 NULL で MenuId を使用して、関連するメニューのないお気に入りを保存します。しかし、これらの行はユーザーとレシピのペアごとに最大1つだけ必要です。

今までのアイデアとしては

1. nullの代わりに、ハードコードされたUUID（すべて0など）を使用する。
   
   しかし MenuId は、各ユーザーのメニューにFK制約があるので、ユーザーごとに特別な"null"メニューを作成しなければならず、手間がかかりますね。

2. 代わりにトリガーを使用して、NULLエントリの存在をチェックします。
   
   私はこれが面倒だと思うので、可能な限りトリガーは避けたいのです。それに、私のデータが決して悪い状態にならないことを保証してくれるとは思えません。

3. 忘れて、ミドルウェアやinsert関数でNULLエントリーの前歴をチェックし、この制約をつけないようにすればいいのです。

Postgres 9.0を使用しています。

何か見落としている方法はないでしょうか？

解決方法は？

作成 2つのパーシャルインデックス :

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;

この方法では、以下の組み合わせは1つだけです。 (user_id, recipe_id) ここで menu_id IS NULL という制約を効果的に実装しています。

考えられる欠点

• を参照する外部キーは持てません。 (user_id, menu_id, recipe_id) . (3カラム幅のFK参照を望むことはまずないでしょう。代わりにPKカラムを使いましょう!)
• をベースにすることはできません。 CLUSTER を部分インデックスで指定します。
• にマッチしないクエリ WHERE 条件は、部分インデックスを使用できません。

が必要な場合は コンプリート インデックスを削除することができます。 WHERE の条件を favo_3col_uni_idx で、要件はそのまま実行されます。

テーブル全体を構成するようになったインデックスは、他のインデックスと重なり合って大きくなっていきます。典型的なクエリと NULL の値がある場合、これは役に立つかもしれないし、立たないかもしれない。極端な場合、3つのインデックス（2つの部分インデックスとその上の合計）を維持することが役に立つかもしれません。

を解決することができます。 単一Nullableカラム 2つなら可能かもしれません。しかし、それ以上の数ではすぐに手に負えなくなります。なぜなら、NULLにできるカラムの組み合わせごとに別の部分インデックスが必要になり、その数は二項式に増えていくからです。例えば 複数のNullableカラム を参照してください。

• UNIQUE制約がトリガーされないのはなぜですか？

余談：私は PostgreSQL の大文字と小文字が混在した識別子 .