postgresql - 如何在 PostgreSQL 上添加条件唯一索引

Question

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

product_id
variant_id

variant_id 可以为空。

条件是:

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

这在 PostgreSQL 中可能吗？

Answer 1

创建一个 UNIQUE multicolumn index在 (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 值不被认为是相同的.
为了弥补这一点，另外创建一个 partial UNIQUE index在 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) and (1, NULL)，但是两者都不是他们第二次。还可以加快对一列或两列条件的查询。

dba.SE 上最近的相关回答，几乎直接适用于您的情况:

• PostgreSQL multi-column unique constraint and NULL values