0

SQL Server allows you to apply a UNIQUE constraint, but its behaviour is odd: you cannot have more than one NULL in such a column.

My reading of the meaning of NULL and its normal behaviour, as well as the way other DMBS products work, is that NULL should be ignored. Instead, it appears to be compared, and so multiple NULLs are treated as duplicates.

I would regard this as contrary to the normal behaviour of NULL, as well as unfortunate. For example, it should be possible to indicate that a mobile phone number, if known, is unique.

If that is the case, how can we define a UNIQUE column which does allow more than one NULL ?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Manngo
  • 14,066
  • 10
  • 88
  • 110

1 Answers1

1

In SQL Server 2008 and newer, you can create filtered indices that e.g. exclude NULL values:

CREATE NONCLUSTERED UNIQUE INDEX IX_SomeName
ON dbo.YourTableName(Columns)
WHERE Column1 IS NOT NULL
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459