Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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.

share|improve this answer
2  
If you want to ALLOW dupes where all three columns are NULL you can also do a filtered unique index instead of a constraint. –  JNK Oct 30 '13 at 13:37
    
I'm not sure if you meant the word "standard" in the "standard unique constraint" as ironic or not ;) –  ypercube Oct 30 '13 at 14:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.