Postgres で Null 許容列を含む一意の制約を作成する方法

PostgreSQL での Null 許容列の一意制約の処理

このドキュメントでは、Null 許容列を含む PostgreSQL テーブルに一意の制約を適用するという課題について説明します。 この問題は、デフォルトでは NULL 値が互いに区別されるとみなされるために発生します。 これにより、NULL 許容列が NULL を保持する場合、複数の行に同一の非 NULL 値が許可されます。 このソリューションは、NULL を同等のものとして扱う一意の制約を作成することを目的としています。

問題への対処

目的は、NULL 値を許容する列に NULL 値が含まれている場合でも、特定の列に基づいて重複行を防止する一意制約を作成することです。

ソリューション

PostgreSQL 15 以降:

PostgreSQL 15 では NULLS NOT DISTINCT 句が導入され、簡単なソリューションが提供されました。この句により、NULL 値が一意性制約内で同一であるとみなされるようになります。 次のコマンドはその使用法を示しています:

<code class="language-sql">ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);</code>

PostgreSQL 14 以前:

古い PostgreSQL バージョンの場合、NULLS NOT DISTINCT 句は使用できません。 この解決策には、2 つの部分的な一意のインデックスを作成する必要があります:

<code class="language-sql">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;</code>

このアプローチは、一意性を効果的に強制します。最初のインデックスは menu_id が NULL でない場合を処理し、2 番目のインデックスは menu_id が NULL である場合を処理します。 これにより、(user_id, recipe_id) が NULL の場合に menu_id の重複した組み合わせが防止されます。

以上がPostgres で Null 許容列を含む一意の制約を作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。