Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a business requirement to have a FK field on TableA referencing TableB. If there is a value in TableBID, it should be unique, but it's not mandatory, so there could be multiple records with null values.

Is it possible somehow to enforce this type of uniqueness on the DB level, using either an index or a constraint?

share|improve this question
1  
Yes. Use a unique filtered index that excludes NULLs. I guess this must be a duplicate. –  Martin Smith Mar 7 '13 at 9:46
    
@MartinSmith Where's the duplicate? –  Shaul Behr Mar 7 '13 at 9:48
    
Couldn't find an exact dupe. Surprised it hasn't been asked before... –  Martin Smith Mar 7 '13 at 9:49
1  
@MartinSmith it was asked before, but on StackOverflow, not dba.se –  Shaul Behr Mar 7 '13 at 9:56

2 Answers 2

up vote 7 down vote accepted

You can create a unique filtered index as below.

CREATE UNIQUE NONCLUSTERED INDEX IX ON TableA(TableBID) WHERE TableBID IS NOT NULL

For versions of SQL Server prior to filtered index support (pre 2008) see this related question.

share|improve this answer
    
Perfect, thanks! –  Shaul Behr Mar 7 '13 at 9:54

I am pretty sure you cannot do this with SQL Server. Having nulls violates the principle of uniqueness in SQL Server. I have seen a post on this before: HERE

It points to a page that suggests having a computed column as a unique key, which is a handy solution.

Alternatively, you could manually enforce the uniqueness with a table trigger. When a value is inserted / updated, manually check the uniqueness. This way opens many other options to you too, such as custom erroring, auditing etc.

share|improve this answer
1  
Incorrect - see accepted answer for how to do it –  Shaul Behr Mar 7 '13 at 9:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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