Be Unique Or Be Null

We all knew that unique index can prevent duplicated values in the table. Does a column with unique constraint allow NULL values? The answer is positive. But only one. This is what this post for.

There are chances that we might need the value in a column to be unique, or be NULL. Take an example, you have a user table for your online social application. The user can be validated with their identities (PID) or be linked with another external ID like Live ID (now calls Microsoft Account) in the real world. Otherwise he can choose to stay as not validated. To avoid duplicated registration, you might need the PID to be unique. At the same time, user can also choose to keep their privacy, and leave the PID value to be empty.

For the scenario above, you will find some difficulties to define the unique constraint. There will be impossible to save two unverified user, because their PIDs are all NULL.

To resolve this problem, you can use filtered index which was first introduced in SQL Server 2008. You can define the unique index as
CREATE UNIQUE INDEX uix_users_pid ON Users (PID) WHERE PID IS NOT NULL

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: