SQL Server Unique Constraint on two columns with an exception 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 &lt;&gt; 'NA';
You can create a unique index with a where clause to filter out those values for which you want to avoid duplicates.