如何在 PostgreSQL 中使用 NULL 欄位強制執行唯一約束？

PostgreSQL 唯一約束和 NULL 欄位：實用指南

在處理可空列時處理 PostgreSQL 中的唯一限制需要仔細考慮。 想像一個包含 UserId、MenuId 和 RecipeId 欄位的表，其中 UserId 和 RecipeId 為非空。 如果 MenuId 不同，這些欄位上的標準唯一限制將允許重複條目，包括 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 版本，建議的方法包括建立 部分索引:

<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 和 NOT NULL 的行建立單獨的索引來有效地強制唯一性。

部分索引的重要注意事項

使用部分索引會帶來一些限制：

• 外鍵約束不能直接套用於三列組合（user_id、menu_id、recipe_id）。
• 不支援基於部分索引的聚類。
• 缺少符合WHERE子句的查詢將不會使用部分索引。

最佳實務：

建議在 PostgreSQL 中使用小寫識別碼（例如 favorites）以保持一致性並避免潛在問題。 選擇合適的方法取決於您的 PostgreSQL 版本和特定需求。 NULLS NOT DISTINCT 子句在新版本中提供了更清晰的解決方案，而部分索引為舊版本提供了功能替代方案。

以上是如何在 PostgreSQL 中使用 NULL 欄位強制執行唯一約束？的詳細內容。更多資訊請關注PHP中文網其他相關文章！