PostgreSQL で NULL 列を使用して一意の制約を強制する方法

PostgreSQL の固有制約と NULL 列: 実践ガイド

Null 許容列を扱う場合、PostgreSQL で一意の制約を処理するには、慎重な検討が必要です。 UserId、MenuId、および RecipeId 列があり、UserId と RecipeId が非 null であるテーブルを想像してください。 これらの列に対する標準の一意制約では、MenuId が異なる場合 (NULL 値を含む) に重複エントリが許可されます。

これに対処するための効果的な戦略は次のとおりです:

PostgreSQL 15 以降のバージョン

PostgreSQL 15 では NULLS NOT DISTINCT 句が導入され、簡単なソリューションが提供されました。この句は、制約とインデックス内で一意性を強制するときに NULL 値を等しいものとして扱います:

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

PostgreSQL 14 以前のバージョン

古い PostgreSQL バージョンの場合、推奨されるアプローチには、部分インデックス:

の作成が含まれます。

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 である行と NULL ではない行に個別のインデックスが作成されるため、効果的に一意性が強制されます。

部分インデックスに関する重要な考慮事項

部分インデックスを使用すると、いくつかの制限が生じます:

• 外部キー制約を 3 つの列の組み合わせ (user_id、menu_id、recipe_id) に直接適用することはできません。
• 部分インデックスに基づくクラスタリングはサポートされていません。
• 一致する WHERE 句がないクエリでは、部分インデックスは使用されません。

ベストプラクティス:

一貫性を確保し、潜在的な問題を回避するために、PostgreSQL では小文字の識別子 (favorites など) を使用することをお勧めします。 適切な方法の選択は、PostgreSQL のバージョンと特定のニーズによって異なります。 NULLS NOT DISTINCT 句は新しいバージョンでよりクリーンなソリューションを提供し、部分インデックスは古いバージョンの機能的な代替手段を提供します。

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