Create a unique constraint on a column that allows null


WHAT

A unique constraint that allows for nulls is exactly as it sounds. Essentially we allow any number of rows to have a value of null for a column unless a column has a value, then we want to enforce uniqueness. This is different than a regular constraint in that normally you would only be allow one row with a null value in that column

WHY

Say you are tracking VIS (Vaccine Information Statement) in your database. For the last couple of years or so VIS have had an associated bar code. In your database you likely want to ensure that a unique value is stored in the database for a bar code, but only for more recent VIS records where a bar code is available.

SOLUTION

Starting in SQL Server 2008 you can apply a filter to a constraint where it only applies in certain cases. In the case of a VIS example described above we would only want to make sure the bar code is unique if it is a non-null value.

For more information check this stackoverflow post.

Leave a comment