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

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

PostgreSQL では、NULL 値を許可する列全体に一意性制約を適用する際に、特有の課題が生じます。 複数の行が同一の非 NULL 値を共有し、NULL エントリのみが異なる場合があります。 このため、制約定義内で NULL を慎重に処理する必要があります。

PostgreSQL 15 以降: NULLS NOT DISTINCT ソリューション

PostgreSQL 15 以降のバージョンでは、NULLS NOT DISTINCT 句を使用してこのプロセスを簡素化しています。この句を UNIQUE 制約に追加すると、NULL 値が同等のものとして扱われます。 したがって、NULL 許容列に NULL が含まれるかどうかに関係なく、NULL 以外の値の特定の組み合わせを持つ行は 1 つだけ存在できます。

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

PostgreSQL 14 以前: 回避策としての部分インデックス

古い PostgreSQL バージョンの場合、部分インデックスを作成するアプローチが推奨されます。 これらのインデックスはデータのサブセットに一意性を適用し、NULL を個別に効果的に処理します。 1 つのインデックスは NULL 許容列が NULL ではない行をカバーし、もう 1 つは NULL である行を処理します。

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;

重要な考慮事項

部分インデックスは効果的ですが、機能が制限される可能性があります。 外部キーの参照とクラスタリングのオプションを制限することができます。 完全なインデックスが不可欠な場合は、NULL 許容列を含む、より広範な列セットにわたる UNIQUE 制約を検討してください。

もう 1 つのオプションは、すべての状況に適していない可能性がありますが、NULL を許可する列に NULL 以外のデフォルト値を割り当てることです。 これにより制約の作成が簡素化されますが、アプリケーションの要件によってはデータの整合性に影響を与える可能性があります。

最後に、一貫した命名規則が重要です。 PostgreSQL で小文字の識別子を使用すると、可読性が向上し、潜在的な問題が回避されます。

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