MS-SqlServer, by default, doesn’t appear to handle the “UNIQUE” constraint in the fashion prescribed by ANSI. The ANSI approach to the “UNIQUE” seems to allow for multiple null values while enforcing that non-null values are unique. MS-SqlServer’s implementation of the “UNIQUE” constraint does enforce that non-null values are unique but also will allow only one null entry in the constrained column.

There are scenario’s where it is useful for a column (or pair of columns) to be allowed to be either “NULL” or “UNIQUE.” For instance, my organization had experienced a scenario where we were required to collect GPS information with each work activity performed for a customer in the field. On several occasions the employees collecting the work could not get a GPS fix. This did not happen often but did happen often enough that MS implementation of the “UNIQUE” constraint, and its approach to “NULL” values, became an issue.

We were able to work around the MS constraint approach with the use of a calculated column. Essentially, the calculated column assists in enforcing the “UNIQUE” constraint by examining two columns that we wish to be unique or null.

create table multipleNullOK(

     id int NOT NULL IDENTITY PRIMARY KEY,

     longitude int,

     latitude int,

     –gpsLatAndLongCalcCol is my calculated field

     gpsLatAndLongCalcCol as (

          CASE 

               WHEN longitude IS NULL AND latitude IS NULL THEN id 

          ELSE 0 

          END

)
      –The UNIQUE constraint now incorporates the calculated column
     CONSTRAINT UNQ_GPS_LatAndLong_NULL_OK UNIQUE (longitude, latitude, gpsLatAndLongCalcCol)
 

)

 –Examing the empty table

select * from multipleNullOk

–Test the constraint
insert into multipleNullOK (longitude, latitude) values (1, 1) –OK
insert into multipleNullOK (longitude, latitude) values (1, 1) –Fail
insert into multipleNullOK (longitude, latitude) values (1, 2) –OK
insert into multipleNullOK (longitude, latitude) values (null, null) –OK
insert into multipleNullOK (longitude, latitude) values (null, null) –OK

insert into multipleNullOK (longitude, latitude) values (null, null) –OK

insert into multipleNullOK (longitude, latitude) values (1, 1) –Fail
insert into multipleNullOK (longitude, latitude) values (1, 2) –Fail

insert into multipleNullOK (longitude, latitude) values (null, null) –OK

–Examine the table

select * from multipleNullOk

If you have run into this issue with MS-SqlServer this approach may be useful to you as well!