In this post I’ll go over the relationship between unique constraints and NULLs in SQL Server, and how you can have multiple NULLs in a unique constraint.
What is a unique constraint?
A unique constraint is a database-level object that enforces value uniqueness across one or more columns that do not participate in the table’s primary key.
Unique constraints are vital for ensuring data integrity without you having to write additional code that manually checks if a value already exists before it’s inserted.
Quick demo
First, create a table with a unique constraint on one of the columns.
|
1 2 3 4 5 6 7 |
CREATE TABLE [Users] ( [ID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_Users_ID] PRIMARY KEY CLUSTERED, [UserName] NVARCHAR(128) NOT NULL, [EMail] NVARCHAR(128) NULL, CONSTRAINT [UQ_EMail] UNIQUE([EMail]) ); |
Then add a couple of records that match the requirement for the e-mail address to be unique.
|
1 2 3 4 |
INSERT INTO [Users] ([UserName], [EMail]) VALUES (N'User1', N'user_one@mailprovider.com'), (N'User2', N'user_two@mailprovider.com'), (N'User3', NULL); |
At this point, the Users table looks like this:
Now, to test the unique constraint, I try to add a record with an e-mail address that already exists.
|
1 2 |
INSERT INTO [Users] ([UserName], [EMail]) VALUES (N'NewUser', N'user_one@mailprovider.com'); |
Since the email address already exists in the Users table, this insert will fail with a unique constraint violation error.
But what about NULLs?
Even though NULLs aren’t actually values, In SQL Server they also count towards the values that are only accepted once in a column with a unique constraint.
Case in point, this insert also fails with a unique constraint violation error:
|
1 2 |
INSERT INTO [Users] ([UserName], [EMail]) VALUES (N'NewUser', NULL); |
SQL Server vs ANSI SQL
While SQL Server’s unique constraints only accept one occurrence of NULL, the ANSI SQL standard states that uniqueness is only enforced for non-NULL values, and multiple NULLs are allowed.
Other RDBMS, like Oracle and MySQL, adhere to the ANSI SQL standard in this regard by ignoring NULLs in unique constraints.
Enforcing uniqueness while allowing multiple NULLs
A behavior similar to the ANSI SQL definition of unique constraints is achieved in SQL Server with the help of filtered unique indexes.
First, drop the existing unique constraint.
|
1 2 |
ALTER TABLE [Users] DROP CONSTRAINT [UQ_EMail]; GO |
Next, I create a unique index on the EMail column that filters out NULLs.
|
1 2 3 4 |
CREATE UNIQUE INDEX [UIX_Users_EMail] ON [Users]([EMail]) WHERE [EMail] IS NOT NULL; GO |
Now, to test it, I try to insert a record with an e-mail address that already exists in the table.
|
1 2 |
INSERT INTO [Users] ([UserName], [EMail]) VALUES (N'NewUser', N'user_one@mailprovider.com'); |
As expected, the insert fails.
Time to test inserting a couple of NULLs aside from the already existing one.
|
1 2 3 |
INSERT INTO [Users] ([UserName], [EMail]) VALUES (N'NewUser1', NULL), (N'NewUser2', NULL); |
This insert succeeds without any issues.
Even though this isn’t really a constraint type object, since it’s an index, it does behave like a constraint by enforcing uniqueness among non-NULL values in that column.
But what if the exception should be a specific value?
Let’s say that the column doesn’t allow NULLs and, if an e-mail address isn’t provided, an empty string will be inserted instead.
In this case, I drop and re-create the table, but this time I add a default constraint on the EMail column.
|
1 2 3 4 5 6 7 8 9 |
DROP TABLE [Users]; GO CREATE TABLE [Users] ( [ID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_Users_ID] PRIMARY KEY CLUSTERED, [UserName] NVARCHAR(128) NOT NULL, [EMail] NVARCHAR(128) NOT NULL CONSTRAINT [DF_EMail] DEFAULT N'' ); |
Afterward I create the filtered unique index again, but I change the filtering condition so that it excludes empty strings.
|
1 2 3 4 |
CREATE UNIQUE INDEX [UIX_Users_EMail] ON [Users]([EMail]) WHERE [EMail] <> N''; GO |
And I insert some test records.
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO [Users] ([UserName], [EMail]) VALUES (N'User1', N'user_one@mailprovider.com'), (N'User2', N'user_two@mailprovider.com'), (N'User3', N''), (N'User4', N''); INSERT INTO [Users] ([UserName]) VALUES (N'User5'); /*This one fails*/ INSERT INTO [Users] ([UserName], [EMail]) VALUES (N'UserOne', N'user_one@mailprovider.com'); |
Attempting to insert any other value that already exists in the EMail column will still fail, while empty strings will not be taken into account.
The same applies for scenarios where multiple exceptions are needed. The only difference being that the filtering condition will need to be updated, so that it excludes those exceptions.
|
1 2 3 4 |
CREATE UNIQUE INDEX [UIX_Users_EMail] ON [Users]([EMail]) WHERE [EMail] <> N'' AND [EMail] <> N'test_mail@test.com' AND [EMail] <> N'N/A'; GO |
Note that I’ve used AND instead of NOT IN because filtered indexes do not support the latter. Brent Ozar has a great blog post on this topic.
Conclusion
If you have a use case that requires enforcing uniqueness, but requires one or more exceptions, unique filtered indexes are your best bet.
This has the added benefit of being able to use said index in queries that filter by that column, as long as the value in the query’s WHERE clause is not the same value that’s being filtered out in the index.
Leave a Reply