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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
@MartinSmith it was asked before, but on StackOverflow, not – Shaul Behr Mar 7 '13 at 9:56
up vote 7 down vote accepted

You can create a unique filtered index as below.


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
Incorrect - see accepted answer for how to do it – Shaul Behr Mar 7 '13 at 9:56

Your Answer


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.