SQL SERVER: How to add Unique Constraint/Index on a Column that allows Multiple Null values

Introduction: In this article example I am going to share how to add UNIQUE CONSTRAINT/ UNIQUE NONCLUSTERED INDEX on a column that allows Multiple Null values. 

In previous articles I have explained How to Find all primary and foreign key constraints on each or any table in database and Without primary key column update first or last n records in table and Search any text in all stored procedures, views and functions and Find all foreign keys referring to particular table and Merge in sql server to insert, update and delete in single statement

Description: Unique Constraint allows only one NULL value. We cannot insert multiple NULLs. However, there are situations when we need more than one NULL value in the column but still have to maintain uniqueness, ignoring all those NULL values. 

In such cases we can create UNIQUE NONCLUSTERED INDEX with the NOT NULL filter and can insert multiple NULLs.

As we know, when we create a UNIQUE NONCLUSTERED INDEX on a column that allows NULL, SQL Server allows only one NULL value so as to maintain the Uniqueness. Both Unique Constraint and Unique Index are same in maintaining uniqueness. When we create a column using UNIQUE constraint or UNIQUE Index, in both the cases SQL Server by default creates a unique non-clustered index. But there are some differences between these two as listed below. 

1) We can not disable Unique Constraint, but Unique Index can be disabled using the following syntax :
ALTER INDEX Index_name ON Table_name DISABLE
2) We can not add filters in Unique Constraint, but Unique Index allows filters.

So In this article, I am going to explain how to maintain uniqueness on a column and also allow multiple NULL values.

Implementation: Let’s demonstrate by an example.

--Let's  create a dummy employee table using the following script

CREATE  TABLE dbo.tbEmployee

(

            EmployeeId                INT IDENTITY(1,1) PRIMARY KEY,

            EmployeeName         VARCHAR(100) NOT NULL,

            MobileNo                   VARCHAR(15) NULL

)

Now suppose we need to add Unique Index on MobileNo column so that it does not allow duplicate mobile number for any other employee. But it is also possible that for some employees mobile number is not available. So this column also allows null entry.

To add Unique Index on MobileNo column we need to execute the following:

CREATE UNIQUE NONCLUSTERED INDEX UC_MobileNo

ON dbo.tbEmployee(MobileNo)

Now try to insert some dummy entries in employee table using following script

GO

INSERT INTO dbo.tbEmployee (EmployeeName, MobileNo)

VALUES

('Rajan','9898765465'),

('Tarun','9876545678'),

('Swati','7098987676'),

('Arjun',NULL),

('Simran','709898090954'),

('Jatin',NULL),

('Vivek','9988007687')

Sql Server will generate the following error message:

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.tbEmployee' with unique index 'UC_MobileNo'.

The statement has been terminated.

Reason: As we know The UNIQUE Index ensures that all values in a column are different. And in our insert script there are more than one NULL value (For Jatin and Arjun) and it violates the unique index policy.

We need the mobile numbers to be unique but it can also be null. Then how it is possible?

It is possible. We can use where clause while adding unique index. But first we need to delete the previous unique index on the MobileNo column using following script:

DROP INDEX dbo.tbEmployee.UC_MobileNo;

Now add unique index using following:

CREATE UNIQUE NONCLUSTERED INDEX UC_MobileNo

ON dbo.tbEmployee(MobileNo)

WHERE MobileNo IS NOT NULL;

Here we have filtered the check for unique index to check uniqueness where there is a value in MobileNo column i.e. where MobileNo is not null.

 Now try to insert same records once again.

GO

INSERT INTO dbo.tbEmployee (EmployeeName, MobileNo)

VALUES

('Rajan','9898765465'),

('Tarun','9876545678'),

('Swati','7098987676'),

('Arjun',NULL),

('Simran','709898090954'),

('Jatin',NULL),

('Vivek','9988007687')

Query to View employee records

SELECT * FROM dbo.tbEmployee

EmployeeId	EmployeeName	MobileNo
1	Rajan	9898765465
2	Tarun	9876545678
3	Swati	7098987676
4	Arjun	NULL
5	Simran	709898090954
6	Jatin	NULL
7	Vivek	9988007687

As we can see records added successfully without causing any issue. 

Now Let's try to add duplicate Mobile Number (same as rajan's mobile number) for a new entry

INSERT INTO dbo.tbEmployee (EmployeeName, MobileNo)

VALUES

('Surbhi','9898765465')

Sql Server will generate the following error:

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.tbEmployee' with unique index 'UC_MobileNo'.

The statement has been terminated.

As we can see it did not allow duplicate mobile number.

  

Now Let's try to add NULL value in MobileNo column for a new entry

INSERT INTO dbo.tbEmployee (EmployeeName, MobileNo)

VALUES

('Kabeer',NULL)

SELECT * FROM dbo.tbEmployee

EmployeeId	EmployeeName	MobileNo
1	Rajan	9898765465
2	Tarun	9876545678
3	Swati	7098987676
4	Arjun	NULL
5	Simran	709898090954
6	Jatin	NULL
7	Vivek	9988007687
8	Kabeer	NULL

As we can see it allowed null value.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post