top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to create a UNIQUE Key on a Column which is having multiple NULL values?

+1 vote
98 views

In SQL Server, Unique Key Constraint will allow only one NULL value. But there are situations that are more than one null value in the column, but still have to maintain Uniqueness by ignoring all null values.

posted Jun 15, 2015 by Shivaranjini

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Method 1
Only works on SQL Server 2008 and above
Using Filter Index. Filtered index is used to Index a portion of rows in a table. While creating an index, we can specify conditional statements. The below SQL Query will create a Unique Index on the rows having non null values:

CREATE UNIQUE INDEX IX_ClientMaster_ClientCode ON ClientMaster(ClientCode)
WHERE ClientCode IS NOT NULL

Method 2
Create a view having the unique fields and create a Unique Clustered Index on it:

Create View vClientMaster_forIndex
With SchemaBinding
As
Select ClientCode From dbo.ClientMaster Where ClientCode IS NOT NULL;
Go

CREATE Unique Clustered Index UK_vClientMaster_ForIndex
on vClientMaster_forIndex(ClientCode)

Method 3
Create a Computed Column like the following and create a UNIQUE KEY on that:

[CMP_ClientCode]  AS (case when [ClientCode] IS NULL _
then CONVERT([varchar](10),[ClientID],0) else [ClientCode] end)
answer Jun 15, 2015 by Manikandan J
...