CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Cuckoo
Cuckoo

Reputation: 177

How to allow null value to be repeated?

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?

Upvotes: 2

Views: 51

Answers (1)

sqluser
sqluser

Reputation: 5672

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

Upvotes: 1

Related Questions