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.
Now try to insert same records once again.
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.
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.
If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..