Sql Server: Allowing Null in a Unique Constraint

Unique constraints help you to ensure the consistency of your data. As their name suggests, they let you insert a value only once in a column. This includes the special value NULL, what in most cases will not be a problem.

However, sometimes we need to exclude NULL from the uniqueness requirement. For a long time that could only be achieved by dropping the unique constraint in the database and making that check in your application. Since SQL Server 2008 you can use a WHERE clause in your index and keep this check in the database:

The column Token in the table UserToken can now store multiple rows with NULL, but it will not store the same non-NULL value twice.

This little addition of WHERE is a great help and may help you to keep your data in a consistent state.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.