如何在 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中文网其他相关文章！