I'm having a little problem right now, we use this table in our database:
CREATE TABLE offer ( offer_id serial PRIMARY KEY , product_id int NOT NULL REFERENCES product , price_old numeric(10,2); , price numeric(10,2); , price_alt text -- overrules price if present , valid_from timestamp NOT NULL , valid_to timestamp -- optional -- more attributes of the offer , CONSTRAINT some_kind_of_price_required CHECK (price IS NOT NULL OR price_alt IS NOT NULL) );
And we use a unique index which doesn't work:
ALTER TABLE offers ADD CONSTRAINT offer_unique_index UNIQUE(product_id, price_old, price, price_alt, valid_from, valid_to);
It doesn't work because price_old, price and price_alt can be NULL.... It's possible that only price is filled, only price and price_old or only price_alt...
I don't know a good way to fix it. Probably multiple Unique indexes, but I cannot figure out right know how many and how to construct them. Can anybody help me out?