UNIQUE Column with multiple NULL values
As you know, when you create a UNIQUE constraint on a nullable column, SQL Server allows only one NULL value, thereby maintaining the UNIQUEness. 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 this article, I am going to illustrate how to maintain uniqueness on a column and also allow multiple NULL values.
Let us assume that we have a database, MyDB, and we want to create a table that holds social security numbers, as shown below.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
go
Create Database [Employee]
go
use [Employee]
go
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] int NULL)
go
We know social security number is unique, so let us add UNIQUE constraint to the column [Social Security Number].
Alter table Emp Add constraint ssn_unique UNIQUE
([Social Security Number] )
Now let us try to insert some data as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob',111213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob',121213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob',131213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony',131413426)
As we know, social security number is unique. However, in situations of people entering this country using a work visa, it would take sometime for them to get a social security number. Until that time, the data would be NULL.
Now let us try to insert one row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Result
(1 row(s) affected)
Now let us try to insert another row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Result
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'ssn_unique'.
Cannot insert duplicate key in object 'dbo.Emp'.
The statement has been terminated.
This is the normal behaviour of UNIQUE constrain on a NULL column. It allows one row of data with NULL values. However, that is not the behaviour we want for this column. We want the column to accept unique values and also accept multiple NULL values.
This can be achieved using a computed column and adding a contraint to the computed column instead of on the actual Social Security Number column.
Now let us recreate the database from the scratch, only this time we will add a computed column as shown below.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
go
Create Database [Employee]
go
use [Employee]
go
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] int NULL)
go
Alter table Emp Add MySSN as case when [Social Security Number]
is NULL then [Employee id] else [Social Security Number] end
go
Now let us add the UNIQUE constraint to the computed column as shown below.
Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
go
Now let us try to insert some data as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob',111213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob',121213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob',131213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony',131413426)
Let’s try to insert one row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Result
(1 row(s) affected)
Try to insert another row without Social Security Number:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Result
(1 row(s) affected)
If the Social Security Number column is a varchar column then we can create the computed column and the constraint as shown below in order to achieve the same goal of having unqiueness with multiple NULL values.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
go
Create Database [Employee]
go
use [Employee]
go
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] varchar(12) NULL)
go
Let’s create the computed column for Social Security Number as shown below.
Alter table Emp Add MySSN as case when [Social Security Number]
is NULL then convert(varchar(12),[Employee id]) else [Social Security Number] end
go
Now let’s add the UNIQUE constraint to the computed column:
Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
go
Let’s try to insert some data as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob','111-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob','121-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob','131-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony','131-41-3426')
Now try to insert one row without Social Security Number:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Result
(1 row(s) affected)
Now let’s try to insert another row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Result
(1 row(s) affected)
Conclusion
We have created a column on a table, which holds UNIQUE values and also multiple NULL values using Primary key values, computed column and UNIQUE constraint on the computed column.
As you know, when you create a UNIQUE constraint on a nullable column, SQL Server allows only one NULL value, thereby maintaining the UNIQUEness. 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 this article, I am going to illustrate how to maintain uniqueness on a column and also allow multiple NULL values.
Let us assume that we have a database, MyDB, and we want to create a table that holds social security numbers, as shown below.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
go
Create Database [Employee]
go
use [Employee]
go
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] int NULL)
go
We know social security number is unique, so let us add UNIQUE constraint to the column [Social Security Number].
Alter table Emp Add constraint ssn_unique UNIQUE
([Social Security Number] )
Now let us try to insert some data as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob',111213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob',121213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob',131213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony',131413426)
As we know, social security number is unique. However, in situations of people entering this country using a work visa, it would take sometime for them to get a social security number. Until that time, the data would be NULL.
Now let us try to insert one row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Result
(1 row(s) affected)
Now let us try to insert another row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Result
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'ssn_unique'.
Cannot insert duplicate key in object 'dbo.Emp'.
The statement has been terminated.
This is the normal behaviour of UNIQUE constrain on a NULL column. It allows one row of data with NULL values. However, that is not the behaviour we want for this column. We want the column to accept unique values and also accept multiple NULL values.
This can be achieved using a computed column and adding a contraint to the computed column instead of on the actual Social Security Number column.
Now let us recreate the database from the scratch, only this time we will add a computed column as shown below.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
go
Create Database [Employee]
go
use [Employee]
go
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] int NULL)
go
Alter table Emp Add MySSN as case when [Social Security Number]
is NULL then [Employee id] else [Social Security Number] end
go
Now let us add the UNIQUE constraint to the computed column as shown below.
Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
go
Now let us try to insert some data as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob',111213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob',121213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob',131213422)
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony',131413426)
Let’s try to insert one row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Result
(1 row(s) affected)
Try to insert another row without Social Security Number:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Result
(1 row(s) affected)
If the Social Security Number column is a varchar column then we can create the computed column and the constraint as shown below in order to achieve the same goal of having unqiueness with multiple NULL values.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
DROP DATABASE [Employee]
go
Create Database [Employee]
go
use [Employee]
go
Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] varchar(12) NULL)
go
Let’s create the computed column for Social Security Number as shown below.
Alter table Emp Add MySSN as case when [Social Security Number]
is NULL then convert(varchar(12),[Employee id]) else [Social Security Number] end
go
Now let’s add the UNIQUE constraint to the computed column:
Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
go
Let’s try to insert some data as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob','111-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob','121-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob','131-21-3422')
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony','131-41-3426')
Now try to insert one row without Social Security Number:
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)
Result
(1 row(s) affected)
Now let’s try to insert another row without Social Security Number as shown below.
Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)
Result
(1 row(s) affected)
Conclusion
We have created a column on a table, which holds UNIQUE values and also multiple NULL values using Primary key values, computed column and UNIQUE constraint on the computed column.
