如何在 PostgreSQL 中使用可空列创建唯一约束？

管理 PostgreSQL 中的唯一约束和可空列

PostgreSQL 在跨允许 NULL 值的列强制执行唯一性约束时面临着独特的挑战。 多行可能共享相同的非 NULL 值，而仅 NULL 条目不同。 这需要在约束定义中仔细处理 NULL。

PostgreSQL 15 及更高版本：NULLS NOT DISTINCT 解决方案

PostgreSQL 15 及后续版本使用 NULLS NOT DISTINCT 子句简化了此过程。当将此子句添加到 UNIQUE 约束时，会将 NULL 值视为等效值。 因此，无论可空列是否包含 NULL，都只能存在具有给定非 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 版本，推荐的方法涉及创建部分索引。 这些索引强制数据子集的唯一性，有效地单独处理 NULL。 一个索引覆盖可空列不为 NULL 的行，另一个索引处理可空列为 NULL 的行。

<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>

重要注意事项

部分索引虽然有效，但可能会限制功能。 它们可以限制外键引用和集群选项。 如果完整索引是必需的，请考虑跨越更广泛的列集（包括可为空列）的 UNIQUE 约束。

另一个选项，虽然可能不适合所有情况，但为可为空的列分配一个非 NULL 默认值。 这简化了约束创建，但可能会影响数据完整性，具体取决于应用程序的要求。

最后，一致的命名约定至关重要。 在 PostgreSQL 中使用小写标识符可以增强可读性并避免潜在问题。

以上是如何在 PostgreSQL 中使用可空列创建唯一约束？的详细内容。更多信息请关注PHP中文网其他相关文章！