Enforcing Uniqueness On A Nullable Column

19 May, 2009 (11:04) | T-SQL | By: Mark V

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.

http://weblogs.sqlteam.com/brettk/archive/2005/04/20/4592.aspx

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.

USE tempdb

GO

 

CREATE TABLE Superhero

(

       SuperheroID int NOT NULL IDENTITY

     , SuperheroName varchar(25) NULL

)

 

INSERT INTO Superhero (SuperheroName) VALUES('Superman')

INSERT INTO Superhero (SuperheroName) VALUES('Wonder Woman')

INSERT INTO Superhero (SuperheroName) VALUES('Spiderman')

 

SELECT 

       SuperheroID

     , SuperheroName

FROM Superhero

 

/*

RESULTS:

 

SuperheroID SuperheroName

----------- -------------------------

1           Superman

2           Wonder Woman

3           Spiderman

 

*/

 

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.

ALTER TABLE Superhero ADD SidekickName varchar(25)

 

INSERT INTO Superhero (SuperheroName, SidekickName) VALUES('Batman', 'Robin')

INSERT INTO Superhero (SuperheroName, SidekickName) VALUES('Dangermouse', 'Penfold')

 

SELECT 

       SuperheroID

     , SuperheroName

     , SidekickName

FROM Superhero

 

/*

RESULTS:

 

SuperheroID SuperheroName             SidekickName

----------- ------------------------- -------------------------

1           Superman                  NULL

2           Wonder Woman              NULL

3           Spiderman                 NULL

4           Batman                    Robin

5           Dangermouse               Penfold

 

*/

 

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.

CREATE UNIQUE NONCLUSTERED INDEX ixSidekick ON Superhero (SidekickName ASC)

 

Here is the error returned:

Msg 1505, Level 16, State 1, Line 51

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Superhero' and the index name 'ixSidekick'. The duplicate key value is (<NULL>).

The statement has been terminated.

 

Blast!

< 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.

CREATE VIEW dbo.vSuperheroSidekick

WITH SCHEMABINDING

AS

SELECT

       SuperheroID

     , SuperheroName

     , SidekickName

FROM dbo.Superhero

WHERE SidekickName IS NOT NULL

 

Let’s query our view.

 

SELECT

       SuperheroID

     , SuperheroName

     , SidekickName

FROM vSuperheroSidekick

 

/*

RESULTS:

 

SuperheroID SuperheroName             SidekickName

----------- ------------------------- -------------------------

4           Batman                    Robin

5           Dangermouse               Penfold

 

*/

 

Fabtastical. So far so good. Now this is where it gets cool. Let’s create our UNIQUE CLUSTERED index on our new view.

CREATE UNIQUE CLUSTERED INDEX ix_vSuperheroSidekick_SidekickName_Unique

ON vSuperheroSidekick (SidekickName ASC)

GO

 

Now, let try inserting a new superhero, Safetyrat, with his diminutive sidekick, Penfold (sound familiar to you?):

 

INSERT INTO Superhero (SuperheroName, SidekickName) VALUES('Safetyrat', 'Penfold')

 

We get the following error:

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.vSuperheroSidekick' with unique index 'ix_vSuperheroSidekick_SidekickName_Unique'.

The statement has been terminated.

 

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:

DROP VIEW dbo.vSuperheroSidekick

DROP TABLE Superhero

Comments

Comment from Aaron Bertrand
Time December 10, 2009 at 3:54 pm

In SQL Server 2008 you can use a filtered index for this, something like this:

CREATE UNIQUE INDEX foo ON dbo.bar(NULLable_column)
WHERE NULLable_column IS NOT NULL;

Comment from Mark V
Time December 11, 2009 at 11:24 am

That is really cool. The client is on 2005 still so it would not help them yet. But this is great info. Thanks much. Definitely worth looking at. :)

Pingback from Bluedog67 | Simple Indexed View To Constrain Data
Time January 7, 2010 at 2:24 pm

[…] appears to work ok. Mark Vaillancourt (Blog | Twitter) wrote a similar post several months ago here. Please share any thoughts or feedback you may have. Thanks! IF […]

Comment from silentpr
Time April 2, 2010 at 11:25 pm

You have tested it and writing form your personal experience or you find some information online?

Comment from Mark V
Time April 15, 2010 at 4:36 pm

Silentpr, I got the info from the link I mention above in which Adam Machanic posted this strategy in comments. I have implemented this for a client on SQL 2005. As Aaron Bertrand mentions above, SQL 2008 features Filtered Indexes which will allow you to accomplish the same goal.

Comment from TaZmAn
Time April 17, 2010 at 9:21 am

How you find ideas for articles, I am always lack of new ideas for articles. Some tips would be great

Comment from Mark V
Time April 24, 2010 at 11:40 am

TaZmAn, my ideas pretty much come from obstacles I encounter and overcome. I look at things I learn and keep in mind that any info/technique I find useful will very likely be useful to someone else, too.

Write a comment