How to allow null value to be repeated?

Question

I have used this on my table

alter table Draftsmen
  ADD constraint uc_draftsmen UNIQUE([DraftsmanCNICNo])

It works but it also doesn't allow NULL values to be repeated since it's a unique constraint.

I want to allow NULL to be repeated. How to do in this constraint?

Possible duplicate of [How to create a unique index on a NULL column?](http://stackoverflow.com/questions/191421/how-to-create-a-unique-index-on-a-null-column) — Breeze, Apr 09 '16 at 08:41

score 1 · Accepted Answer · answered Apr 09 '16 at 08:04

Standard SQL allows that but in SQL Server, you cannot do that.

Instead in SQL Server 2008 and above, you can create a unique filtered index and exclude NULLs

CREATE UNIQUE NONCLUSTERED INDEX Idx_columnName
ON tableName(columnName)
WHERE columnName IS NOT NULL

1 Answers1