How to allow nulls in unique fields

An interesting workaround using unique fields

Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index.

Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.

The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.

First, let’s see the regular behavior of unique fields:

CREATE TABLE testunique
  (
     id    INT IDENTITY(1, 1),
     value VARCHAR(30) NULL UNIQUE
  )

go

— The first two inserts will work
INSERT INTO testunique
VALUES      (‘value1’)

INSERT INTO testunique
VALUES      (NULL)

go

–Next two inserts will fail, they aren’t unique  
INSERT INTO testunique
VALUES      (‘value1’)

go

INSERT INTO testunique
VALUES      (NULL)

go

DROP TABLE testunique 

Now let reapeat the same example again, using a filtered index:

CREATE TABLE testunique
  (
     id    INT IDENTITY(1, 1),
     value VARCHAR(30) NULL 
  )

go

— Creating the filtered index
CREATE UNIQUE INDEX indunique
  ON testunique(value)
  WHERE value IS NOT NULL

go

— The first two inserts will work
INSERT INTO testunique
VALUES      (‘value1’)

INSERT INTO testunique
VALUES      (NULL)

go

–This insert will fail, the first value is already present  
INSERT INTO testunique
VALUES      (‘value1’)

go

— this second insert will work, null values are not part
— of the index, therefore duplicate values are accepted
INSERT INTO testunique
VALUES      (NULL)

go

DROP TABLE testunique 

  • 5074 views

  • Rate
    [Total: 3    Average: 4.3/5]
  • Сергей Агарок

    The second insert will not work too due to unique constraint.

    • Dennes Torres

      Sorry, it was a mistake in the code, the unique constraint should not be created on the 2nd script, thank you !

  • Alex Han

    Filtered index would be good in most cases.
    But, if the set options are different from the initial state when the filtered index defined, insert fails. Usually, JDBC connection connection option setting is different from SSMS default.

    At the second insert,

    set ANSI_NULLS OFF
    GO

    INSERT INTO testunique
    VALUES (NULL)
    GO
    /*
    Msg 1934, Level 16, State 1, Line 30
    INSERT failed because the following SET options have incorrect settings: ‘ANSI_NULLS’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
    */

  • Keith Rowley

    This is a really cool solution to this problem.