Unique Constraint That Allow Multiple Nulls In SQL Server

Recently I came across a requirement to create a unique column which should allow multiple nulls and apply uniqueness on anything other than null. Starting from SQL Server version 2008 we can use the filtered unique index to create a unique constraint that allow multiple nulls. Here is the syntax and an example for creating a unique constraint that allows multiple nulls.

Syntax

Here is the syntax of the Unique Index with filter to allow multiple nulls

CREATE UNIQUE INDEX [Index Name]
ON [Table Name]([Column Name])
WHERE [Column Name] IS NOT NULL;

Example

In this example, I’ve created a table and a unique constraint with a NOT NULL filter. This filter condition will make sure the unique constraint ignores all the NULLs and apply uniqueness to the values other than NULL. Then I’m inserting several records with nulls in the unique column. You will notice, that you can insert more than one null value in the unique constraint column. However, it you try to insert another duplicate value, you will get the “Cannot insert duplicate key…” error.

/* Create a table */
CREATE TABLE dbo.MTB_Table_C (EmpID INT NOT NULL, EmpCode VARCHAR(10) NULL);

/* Create a filtered index on the colum EmpCode */
CREATE UNIQUE INDEX IX_EmpCode_NotNull
	ON dbo.MTB_Table_C(EmpCode) WHERE EmpCode IS NOT NULL;

/* Insert values with multiple nulls */
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (1, 'A00A01')
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (2, NULL)
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (3, 'A00A03')
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (4, NULL)
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (5, 'A00A05')
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (6, NULL)

As you have seen in the above example, you can insert any number of NULL to the unique column. Now, try to insert a duplicate value. You will get the error as seen below.

/* Try to insert a duplicate value in EmpCode */
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (5, 'A00A05')

/* You will get an error */
Msg 2601, Level 14, State 1, Line 243
Cannot insert duplicate key row in object 'dbo.MTB_Table_C' with unique index 'IX_EmpCode_NotNull'. The duplicate key value is (A00A05).
The statement has been terminated.

Related Articles

• See more SQL Server Tips.

Reference

• About filtered index ad Microsoft Docs.