SQL Server: Allow Multiple NULL Values in UNIQUE Constraint

 

In this post, I am sharing a demonstration on how to allow multiple NULLs for UNIQUE Constraint in SQL Server.

As per the ANSI, UNIQUE constraint allows multiple NULLs. But in the SQL Server, it allows only one NULL value.
With the UNIQUE constraint, you cannot insert multiple NULLs.

But you can create UNIQUE NONCLUSTERED INDEX with the NOT NULL filter and can insert multiple NULLs.

Please check the below demonstration:

Create a sample table with UNIQUE Constraint:

1 2	CREATE TABLE tbl_TestUnique (ID INT UNIQUE) GO

Try to insert multiple NULLs:

1 2 3 4 5 6 7

INSERT INTO tbl_TestUnique VALUES (1),(NULL),(NULL),(2),(NULL) GO     Msg 2627, Level 14, State 1, Line 4 Violation of UNIQUE KEY constraint 'UQ__tbl_Test__3214EC26DBD9CD71'. Cannot insert duplicate key in object 'dbo.tbl_TestUnique'. The duplicate key value is (<NULL>).

Now, try to insert with single NULL:

1 2 3

INSERT INTO tbl_TestUnique VALUES (1),(2),(NULL) GO

Drop the table:

1 2	DROP TABLE tbl_TestUnique GO

Create the same table with UNIQUE NONCLUSTERED INDEX:

1 2 3 4 5 6 7

CREATE TABLE tbl_TestUnique (ID INT) GO   CREATE UNIQUE NONCLUSTERED INDEX idx_tbl_TestUnique_ID ON tbl_TestUnique(ID) WHERE ID IS NOT NULL GO

Now, INSERT Multiple Nulls:
As we defined UNIQUE on ID NOT NULL, so we can insert multiple NULLs.

1 2 3 4 5 6 7

INSERT INTO tbl_TestUnique VALUES (1),(2),(NULL),(NULL) GO   INSERT INTO tbl_TestUnique VALUES (NULL),(NULL),(NULL) GO