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. SQL Server unique constraint that allow 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

Reference

Related Articles & Tools

2 comments for “Unique Constraint That Allow Multiple Nulls In SQL Server

  1. Victor
    Feb 28, 2019 at 6:45 am

    This is nice. You should note that this will not work on existing tables with data.

    • Feb 28, 2019 at 10:42 am

      Hi Victor,

      Thank you. Regarding the creation of filtered index on existing table with data: You will not be able to create the filtered index only if the column has duplicate values. In such case you will get error as below. If there is no duplicate other than nulls, then you can create this index.

      Msg 1505, Level 16, State 1, Line 7
      The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.MTB_Table_C’ and the index name ‘IX_EmpCode_NotNull’. The duplicate key value is (A00A05).
      The statement has been terminated.

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.