Unique constraint on multiple nullable columns Sql Server

Question

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

Answer 1

There's nothing special that would prevent you from using a standard unique constraint here, unless there's something you aren't telling us.

When a unique constraint is created on a set of nullable columns, NULL is treated (in terms of uniqueness) like any other of the distinct values in the set. (I believe this behaviour varies across RDBMSes.)

So I think the only case you might have to protect against here is the where all three columns contain NULL at the same time. If this is invalid for your business logic, also create a CHECK constraint to disallow this combination.

• JNK: If you want to ALLOW dupes where all three columns are NULL you can also do a filtered unique index instead of a constraint.
• ypercubeᵀᴹ: I'm not sure if you meant the word "standard" in the "standard unique constraint" as ironic or not ;)