We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null). We are using this table to figure out which price to assign various combos of merchandize.
a_id = id of region b_id = id of merchandize category c_id = id of customer category
so basically, we always know a, b and c, but we have a flexible discounting scheme such that every sale is discounted by the most specific rule we can find.
Our problem is our inability to guard against duplicates in the database, we would ideally want to guard against two rows having the same values in a_id, b_id AND c_id, for instance
a_id=1, b_id=1, c_id=1 and a_id=1, b_id=1, c_id=1
are not to be allowed and
a_id=1, b_id=1, c_id=null and a_id=1, b_id=1, c_id=null
are not to be allowed but
a_id=1, b_id=1, c_id=null and a_id=1, b_id=null, c_id=null
ARE to be allowed as these are not identical in respects to these 3 columns.
I have not found an answer anywhere to how to create a unique constraint that BOTH spans multiple columns AND allows nulls BUT disallows "identical" (at least in respect to the columns specified in the constraint) rows