sql - 如何在多列的唯一索引中允许一列为 NULL 值

Question

这似乎是一个非常简单的问题，但我似乎无法在网上的任何地方找到具体的答案。

我有一个 price 模型，它引用了报价和订单项，因此它有 quote_id 和 line_item_id 列。我使用带有 unique: true 的多列索引来防止将同一 line_item 的多个价格附加到报价中。 quote_id 是索引中的第一列。

但是，我希望允许用户将价格添加到尚未报价的line_items。它适用于一个价格，quote_id 为 null 并且存在 line_item_id。但是，唯一索引阻止我将第二个价格附加到同一个 line_item。 quote_id 中的空值被视为唯一值。

有没有办法让唯一约束只在引号不为空时应用？
我知道 allow_nil 可以用在模型验证中，但是它可以用在索引中吗？

我在想:

add_index :prices, [:quote_id, :line_item_id], :unique => true, :allow_nil => true

PostgreSQL 和 Rails 4。

Answer 1

Is there any way to make the unique constraint only apply when the quote is not null?

实际上，这是唯一方式。您可以有多个“相同”的条目，其中多列索引中的一个或多个列为 NULL，因为 Postgres 不会为此目的考虑两个相同的 NULL 值(例如在大多数情况下)。

索引中列的顺序对此无关紧要。 ( It matters for other purposes, though. )

确保表本身的基础列可以是 NULL。 (有时会与空字符串 '' 混淆。)

在重新阅读你的问题后，更合适的场景似乎是这个相关的答案:
Create a multicolumn index to enforce uniqueness
这一个也可能有帮助，但问题要求您的相反:
How to add a conditional unique index on PostgreSQL

如果您真的想要一个 partial index只有非空值，您也可以这样做:

CREATE INDEX price_uni_idx ON price (quote_id, line_item_id)
WHERE  quote_id     IS NOT NULL
AND    line_item_id IS NOT NULL;

不过，您并不需要为了您的目的。从索引中排除具有 NULL 值的行以使其更快可能仍然有用。详情在这里:
Indexed ORDER BY with LIMIT 1