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.
This little addition of WHERE
is a great help and may help you to keep your data in a consistent state.