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:

1
2
3
CREATE UNIQUE NONCLUSTERED INDEX UQ_UserTokens_Token_NullAllowed
ON dbo.UserTokens(Token)
WHERE Token IS NOT NULL;

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.

The table allows now null values in the unique column

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