I want to set a Unique Constraint that allows nulls using SQL Server 2014 Express

Question

I want to set a unique constraint that allows nulls using SQL Server 2014 Express. I know this has been asked before but it was asked 5 years ago in this post.

How do I create a unique constraint that also allows nulls?

I am only starting to study SQL so I want to see if any other options have become available since 5 years ago.

I am consdiering using a Unique Filtered Index but i would like to avoid having to use this Index if possible.

Thank you.

Answer 1

The answer(s) to the question you linked still apply and are correct and there seems to be no other way so far.

There is a ticket on Microsoft Connect for this since 2007. As suggested there and here your best options are to use a filtered index or a computed column, e.g.

CREATE TABLE [Orders] (
  [OrderId] INT IDENTITY(1,1) NOT NULL,
  [TrackingId] varchar(11) NULL,
  ...
  [TrackingIdUnique] AS (
      CASE WHEN [TrackingId] IS NULL
      THEN '#' + cast([OrderId] as varchar(12))
      ELSE [TrackingId_Unique] END
  ),
  CONSTRAINT [UQ_TrackingIdUnique] UNIQUE ([TrackingIdUnique])
)