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 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';