☰

NULL列を持つ一意制約を作成する 質問する

tag-icon • tag-icon sql postgresql database-design null referential-integrity
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 (すべてゼロなど) を使用します。
    ただし、MenuId各ユーザーのメニューには FK 制約があるため、ユーザーごとに特別な「null」メニューを作成する必要があり、面倒です。

  2. 代わりにトリガーを使用して null エントリの存在を確認します。
    これは面倒だと思うので、可能な限りトリガーを避けます。さらに、トリガーがデータに悪い状態がないことを保証するとは信じていません。

  3. それを忘れて、ミドルウェアまたは挿入関数に null エントリが以前に存在したかどうかを確認し、この制約を設定しないようにしてください。

Postgres 9.0 を使用しています。見落としている方法はありますか?

ベストアンサー1

Postgres 15以降

Postgres 15 では、 という句が追加されましたNULLS NOT DISTINCT。リリースノート:

  • 一意制約とインデックスで NULL 値を一意でない値として扱えるようにする (Peter Eisentraut)

    以前は、NULL 値は常に個別の値としてインデックス付けされていましたが、 を使用して制約とインデックスを作成することでこれを変更できるようになりましたUNIQUE NULLS NOT DISTINCT。

この句では、null単なる別の値として扱われ、UNIQUE制約同じ値を持つ行が複数あることは許可されませんnull。これでタスクは簡単になります。

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

マニュアルの章に例があります「ユニーク制約」
句は、同じインデックスのすべてのnullキーの動作を切り替えます。 1 つのキーに対しては を同等として扱い、別のキーに対しては を同等として扱わないということはできません。
NULLS DISTINCTはデフォルトのまま (標準 SQL に準拠) であり、明示的に記述する必要はありません。

同じ条項は、UNIQUE索引、 あまりにも：

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

キー フィールドの後の新しい句の位置に注意してください。

Postgres 14 以前

作成する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;

(user_id, recipe_id)この方法では、の組み合わせは 1 つだけとなりmenu_id IS NULL、目的の制約が効果的に実装されます。

考えられる欠点:

  • 外部キー参照を持つことはできません(user_id, menu_id, recipe_id)。(3 列幅の FK 参照が必要になる可能性は低いので、代わりに PK 列を使用してください。)
  • CLUSTER部分インデックスをベースとすることはできません。
  • 一致する条件のないクエリではWHERE部分インデックスを使用できません。

完全なインデックスが必要な場合は、代わりにWHERE条件を削除してfavo_3col_uni_idx、要件を引き続き適用することができます。
テーブル全体を構成するインデックスは、他のインデックスと重複して大きくなります。一般的なクエリと値の割合に応じてnull、これが役立つ場合と役に立たない場合があります。極端な状況では、3 つのインデックスすべて (2 つの部分インデックスと上部の合計) を維持すると役立つ場合もあります。

これは、null 可能列が 1 つ、あるいは 2 つの場合に適したソリューションです。ただし、null 可能列の組み合わせごとに個別の部分インデックスが必要になるため、すぐに手に負えなくなり、その数は二項式で増加します。null 可能列が複数ある場合は、代わりに以下を参照してください。

余談：使わないことをお勧めしますPostgreSQL における大文字と小文字が混在する識別子。

おすすめ記事