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';
  • I highly recommend <> over != - sqlblog.org/2008/03/20/which-to-use-or – Aaron Bertrand Aug 9 at 0:54
  • @Aaron Thank you Aaron. I read the article. Thanks!. Will be using <> as I am comfortable with either. – Tom Schreiner Aug 9 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? – Tibor Karaszi Aug 10 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 at 8:55
3

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

| improve this answer | |
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';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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