Enforcing Uniqueness On A Nullable Column
I recently found myself needing to enforce uniqueness on a column which was nullable. To make things interesting, there were already thousands of records for which the field was already NULL and would remain NULL. A unique index on the column was not possible since I had more than one NULL value. I therefore had to come up with another option. After many deep, profound, brain things inside my head, I had come up with a horribly convoluted solution involving duct tape, a Swiss Army knife, 17 sporks, and a hamster named Ginger. I think we can all see why this would not be the best option.
A colleague pointed me to this blog, featuring a comment by Mr. Adam Machanic.
It involved creating a view which returns only non-NULL values for the column in question and then placing an index on that view. Fabtastic! WAY easier than the solution I was hatching myself. Note that there are rules regarding the creation of indexed views to be aware of. Those rules are detailed here: http://msdn.microsoft.com/en-us/library/ms188783.aspx.
Here is how this works.
We start with a database table containing super heroes. One thing to note about these particular super heroes is that none of them have sidekicks.
Now, we need to import super heroes with sidekicks and bring their sidekicks into our table as well. We are also tasked with ensuring that no two super heroes can have the same sidekick name.
Let’s add a SidekickName column to our table and toss some sidekicked superheroes into our table.
So now we have some sidekicks and some superheroes who tend to work alone and have NULL in the SidekickName field. Just to show I am not making things up when it comes to unique indexes, let’s go ahead and try putting a unique index on the Superhero table and see what happens.
Here is the error returned:
< insert villain-style monologue here concerning the feebleness of the superhero of your choice >
Let’s create our view. There are two important things to notice about this statement below. The view must be created using the WITH SCHEMABINDING option. Also, although I have not been using the two-part table name dbo.Superhero thus far, I MUST do so in order to create this schemabound view.
Fabtastical. So far so good. Now this is where it gets cool. Let’s create our UNIQUE CLUSTERED index on our new view.
Now, let try inserting a new superhero, Safetyrat, with his diminutive sidekick, Penfold (sound familiar to you?):
We get the following error:
HAZZAH! The attempt to inject a lame mockery of the ace, the greatest, has been foiled by our fabulous indexed view. Take THAT Baron Greenback!
NOTE: In my situation, I was dealing with a table that did not have a lot of writes, so that added overhead of maintaining this indexed view was not a concern. You will want to take this into consideration in your own environment.
Don’t forget to clean up after yourself: