如何在多个列的唯一索引中允许一个列的空值

Seems like a pretty straightforward question, but I can't seem to locate the specific answer anywhere online.

这似乎是一个很简单的问题，但我似乎无法在网上找到具体的答案。

I have a price model that references both quote and a line item, thus it has quote_id and line_item_id columns. I am using a multicolumn index with unique: true to prevent multiple prices for the same line_item from being attached to a quote. quote_id is the first column in the index.

我有一个价格模型，它同时引用报价和行项，因此它有quote_id和line_item_id列。我正在使用具有unique: true的多olumn索引，以防止同一line_item的多个价格附加到报价中。quote_id是索引中的第一列。

However, I want to allow users to add prices to line_items that haven't been quoted. It works fine for one price, quote_id is null and line_item_id is present. However, the unique index is preventing me from attaching a second price to the same line_item. The null value in quote_id is being treated as unique.

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

Is there any way to make the unique constraint only apply when the quote is not null?
I know that allow_nil can be used in model validation, but can it be used in the index?

是否有任何方法可以使唯一的约束仅在引用不为空时应用?我知道allow_nil可以用于模型验证，但它可以用于索引吗?

I am thinking something like:

我在想:

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

PostgreSQL and Rails 4.

PostgreSQL和Rails 4。

1 个解决方案

#1

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

是否有任何方法使唯一约束只适用于当引用不是空的时候?

Actually, this is the only way. You can have multiple "identical" entries with one or more of the columns in a multicolumn index being NULL, because Postgres does not consider two NULL values identical for this purpose (like in most contexts).

事实上，这是唯一的办法。您可以有多个“相同的”条目，其中一个或多个列在一个多色索引中为空，因为Postgres不认为两个空值在这个目的上是相同的(就像在大多数上下文中一样)。

The sequence of columns in the index doesn't matter for this. (It matters for other purposes, though.)

索引中列的顺序无关紧要。(不过，这对其他目的也很重要。)

Make sure the underlying columns in the table itself can be NULL. (Sometimes confused with the empty string ''.)

确保表中底层列本身可以为NULL。(有时与空字符串混淆)

After re-reading your question, the more appropriate scenario seems to be this related answer:
Create a multicolumn index to enforce uniqueness
This one may be of help, too, but the question asks for the opposite of yours:
How to add a conditional unique index on PostgreSQL

在重新阅读您的问题之后，更合适的场景似乎是这个相关的答案:创建一个多olumn索引来强制惟一性，这个索引可能也有帮助，但问题的另一个问题是:如何在PostgreSQL上添加一个条件惟一索引

If you actually want a partial index with only non-null value, you can do that too:

如果你想要一个只有非空值的部分索引，你也可以这样做:

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;

You do not need that for your purpose, though. It may still be useful to exclude rows with NULL values from the index to make it faster. Details here:
Indexed ORDER BY with LIMIT 1

但是你不需要为了你的目的。从索引中排除具有空值的行以使其更快，这可能仍然是有用的。这里的细节:索引顺序与限制1

#1