Constraint question 2

[djj55]

Hello, SQL 2008 R2
Is it possible to have a constraint that allows NULL but not duplicate values? Sort of like a unique index but allowing multiple NULLs.

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I encourage you to play around with this a bit before you implement in production!

With SQL2008R2, you can create a filtered unique constraint. All you need to do is filter out the nulls, like this...

CREATE UNIQUE NONCLUSTERED INDEX idx_YourIndexNameHere
ON YourTableNameHere(YourColumnNameHere)
WHERE YourColumnNameHere IS NOT NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Dmitry Korotkevich has a blog post on this exact topic

http://aboutsqlserver.com/2010/09/12/sunday-t-sql-tip-uniqueness-of-nullable-field/

PluralSight Learning Library

 

[djj55]

Thank you both. Great answers.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!