如何在 PostgreSQL 上添加条件唯一索引 [英] How to add a conditional unique index on PostgreSQL

问题描述

我有一个包含以下列的 line_items 表:

I have a line_items table with following columns:

product_id
variant_id

variant_id 可以为空.

这里是条件:

• 如果 variant_id 为 NULL，则 product_id 应该是唯一的.
• 如果 variant_id 有值，则 product_id 和 variant_id 的组合应该是唯一的.

• If variant_id is NULL then product_id should be unique.
• If variant_id has a value then combination of product_id and variant_id should be unique.

这在 PostgreSQL 中可行吗?

Is that possible in PostgreSQL?

推荐答案

创建一个 UNIQUE 多列索引:

Create a UNIQUE multicolumn index on (product_id, variant_id):

CREATE UNIQUE INDEX line_items_prod_var_idx ON line_items (product_id, variant_id);

然而，这将允许 (product_id, variant_id) 的 (1, NULL) 多个条目，因为 NULL 值不被视为相同.
为了弥补这一点，另外创建一个 部分 UNIQUE 索引 product_id:

However, this would allow multiple entries of (1, NULL) for (product_id, variant_id) because NULL values are not considered identical.
To make up for that, additionally create a partial UNIQUE index on product_id:

CREATE UNIQUE INDEX line_items_prod_var_null_idx ON line_items (product_id)
WHERE variant_id IS NULL;

这样可以输入(1,2)、(1,3) 和(1, NULL)，但都不能输入他们第二次.还可以通过一列或两列的条件加快查询速度.

This way you can enter (1,2), (1,3) and (1, NULL), but neither of them a second time. Also speeds up queries with conditions on one or both column.

最近在 dba.SE 上的相关答案，几乎直接适用于您的案例:

Recent, related answer on dba.SE, almost directly applicable to your case:

• PostgreSQL 多列唯一约束和NULL 值

这篇关于如何在 PostgreSQL 上添加条件唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！