Simple Talk is now part of the Redgate Community hub - find out why

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 

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue