PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

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