Geek asks and answers

How to allow NULL for a single column in a unique index on multiple columns

It seems like a pretty simple question, but I cannot find a specific answer anywhere on the Internet.

I have a model pricethat refers to both a quote and a position, so it has columns quote_idand line_item_id. I use a multi-column index c unique: trueto prevent multiple prices from adding the same line_item to the quote. quote_idis the first column in the index.

However, I want users to add prices for line_itemswhich were not specified. It works fine for a single price, quote_idnull and line_item_id. However, the unique index prevents me from pegging the second price to the same line_item. Zero value in is quote_idconsidered as unique.

Is there a way to make the unique constraint applicable only when the quote is not zero?
I know what allow_nilcan be used in model validation, but can it be used in an index?

I think something like:

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

PostgreSQL and Rails 4.

+3
source share
1 answer

Is there a way to make the unique constraint applicable only when the quote is not zero?

. "" , NULL, Postgres NULL, (, ).

. ( .)

, NULL. ( ''.)

, , :

, :
PostgreSQL

, :

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- , . :
ORDER BY LIMIT 1

+2

More articles:


All Articles