2

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';
4
  • I highly recommend <> over != - sqlblog.org/2008/03/20/which-to-use-or Aug 9, 2020 at 0:54
  • @Aaron Thank you Aaron. I read the article. Thanks!. Will be using <> as I am comfortable with either. Aug 9, 2020 at 16:38
  • 1
    A bit of time travel there, referring to Aaron's blog post. :-) Still valid, though. Tom, did the index solve your problem? Aug 10, 2020 at 8:44
  • 1
    Please, please re-post your solution as an answer and remove it from the question, thank you.
    – Andriy M
    Aug 10, 2020 at 8:55

2 Answers 2

3

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

0
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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