How to allow NULL value for one column in unique index on multiple columns
indexing, postgresql, ruby-on-rails, sql, unique-constraint
Solution
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).
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 `''`.)
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
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
Problem
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. 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. 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? I am thinking something like: ``` add_index :prices, [:quote_id, :line_item_id], :unique => true, :allow_nil => true ``` PostgreSQL and Rails 4.