My previous problem is still not fixed: We cannot set a unique index because NULL values are allowed...
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);
But the unique index 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...
Now I might have a solution, but I don't know how to realize it exactly...
I thought if I could use NEWID() or MD5() to create an extra column named "uid" (which contains a hash based on the data of all other columns) and add a unique_index on that "uid" column, it could fix the problem. So when I try to insert the exact same values (prices, valid dates etc.), it will create a same hash (because the values are the same, obviously) and that triggers the unique index violation for the hash column.
Nice idea? But more important: possible?