SQL Server Unique Constraint on two columns with an exception

Question

Hi all and thanks for your advice.

Expense(SupplierID(Foreign Key), DocumentID(vchar))

I understand how to add a simple unique constraint on two columns. However, if DocumentID = 'NA', I would like to ignore the rules of the constraint.

Some suppliers in our system do not provide an invoice id, for example. Therefore, I leave the field NULL. I would like to remove all nulls for the field 'DocumentID' to avoid accounting for the NULLS in my client code.

I am new to SQL Server, but I could figure out how to do this using a trigger. The reason I'm asking here is to see if there is a better way to respond to this scenario or to avoid it by a different design.

Thanks!

Thanks Tibor. I was unable to enter a comment correctly, I suppose. This is what I ended up with using your method.

use CMRBE 
CREATE UNIQUE NONCLUSTERED INDEX idx_SupplierID_DocumentID 
ON Expense(SupplierID, DocumentID) 
WHERE DocumentID <> 'NA';

• Aaron Bertrand: I highly recommend <> over != - sqlblog.org/2008/03/20/which-to-use-or
• Tom Schreiner: @Aaron Thank you Aaron. I read the article. Thanks!. Will be using <> as I am comfortable with either.
• Tibor Karaszi: A bit of time travel there, referring to Aaron's blog post. :-) Still valid, though. Tom, did the index solve your problem?
• Andriy M: Please, please re-post your solution as an answer and remove it from the question, thank you.

Answer 1

You can create a unique index with a where clause to filter out those values for which you want to avoid duplicates.

Answer 2

Thanks Tibor. This is what I ended up with and it fits the bill.

use CMRBE 
CREATE UNIQUE NONCLUSTERED INDEX idx_SupplierID_DocumentID 
ON Expense(SupplierID, DocumentID) 
WHERE DocumentID <> 'NA';