How to Create Unique Constraint With NULL Columns in SQL Server

SQL Server is a versatile database developed by Microsoft and is one of the most used databases in the world. It provides the developer with a set of functionalities to create tables, insert data in them and then manipulate and play with them as and when necessary. In this article, we are going to see how we can create unique constraints with NULL columns in SQL Server.

UNIQUE Constraint

The UNIQUE constraint is used to ensure that the column has no duplicate values. However, unlike the PRIMARY KEY constraint, the UNIQUE constraint allows the column to have duplicate values.

Example of UNIQUE Constraint

To understand the UNIQUE constraint better, let’s go through an example. We will first create an employee table using the following query

-- create
CREATE TABLE EMPLOYEE (
  empId INT UNIQUE,
  name VARCHAR(100),
  dept VARCHAR(50)
);

Example 1

As you can notice, we have added the UNIQUE constraint in the empId column. Now lets add some records to the table. We will use the following query to add the records:

-- insert
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (100, 'Clark', 'Engineering');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (5, 'Jill', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (10, 'Dave', 'Accounting');

The above statements inserts three records to the table. We can even insert NULL values to the empId column.

Example 2

The following statement inserts NULL value to the column:

INSERT INTO EMPLOYEE(empId,name,dept) VALUES (NULL, 'Tom', 'Sales');

The following is the current data in the table:

Output

Example 3

Now if we try to insert an already existing value to the column, we will get an error. Like the following statement will throw an error when we try to execute:

INSERT INTO EMPLOYEE(empId,name,dept) VALUES (5, 'Ava', 'Marketing');

The following is the error message:

Violation of UNIQUE KEY constraint 'UQ__EMPLOYEE__AFB3EC0CA984634C'.

UNIQUE Constraint With NULL Value

We might expect that when we insert NULL values twice. We should not get an error as NULL is no real value.

INSERT INTO EMPLOYEE(empId,name,dept) VALUES (NULL, 'Jake', 'Sales');

Output:

Violation of UNIQUE KEY constraint 'UQ__EMPLOYEE__AFB3EC0CA984634C'.

Explanation: We might not want this behaviour as we should be able to create records with NULL values multiple times.

Unique Constraint Using NONCLUSTERED INDEX

To solve this above Problem we will create the table. As before we will create the employee table using the following statement:

CREATE TABLE EMPLOYEE 
(
  empId int,
  name varchar(100),
  dept varchar(50)
);

Explanation: Note that we have not added the UNIQUE constraint to the table while creating the table.

Now we will run the following statement to create the UNIQUE constraint:

CREATE UNIQUE NONCLUSTERED INDEX idx_empId_notnull
ON EMPLOYEE(empId)
WHERE empId IS NOT NULL;

The above statement creates a UNIQUE constraint which allows multiple NULL values.

Explanation:

• CREATE UNIQUE NONCLUSTERED INDEX idx_empId_notnull: This part of the statement creates a non-clustered unique index named idx_empId_notnull.
• ON EMPLOYEE(empId): This part of the statement specifies on which column the index is to be made, in this case the empId column.
• WHERE empId IS NOT NULL: This part is used to apply the index on only non-null values.

Note: For the above statement to work we need to first run the below statement:

SET QUOTED_IDENTIFIER ON;

Now that we have the constraint in place, lets try to insert values in it now. The following statements inserts three records in the table:

INSERT INTO EMPLOYEE(empId,name,dept) VALUES (NULL, 'Clark', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (NULL, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (3, 'Ava', 'Sales');

If we run the following statement now to get the data:

SELECT * FROM EMPLOYEE;

Output:

Output

Now if we try to add the following record to the table:

INSERT INTO EMPLOYEE(empId,name,dept) VALUES (3, 'Jill', 'Marketing');

Output:

Cannot insert duplicate key row in object 'dbo.EMPLOYEE' with unique index 'idx_empId_notnull'.

Explanation: As the following empId already exists in the table, we receive an error. So the UNIQUE constraint created works as intended.

The following is the complete code that we used in this section.

SET QUOTED_IDENTIFIER ON;

-- create table
CREATE TABLE EMPLOYEE (
  empId int,
  name varchar(100),
  dept varchar(50)
);

-- create unique constraint
CREATE UNIQUE NONCLUSTERED INDEX idx_empId_notnull
ON EMPLOYEE(empId)
WHERE empId IS NOT NULL;

-- insert
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (NULL, 'Clark', 'Sales');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (NULL, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE(empId,name,dept) VALUES (3, 'Ava', 'Sales');

-- fetch 
SELECT * FROM EMPLOYEE;

Technical Example

Let’s go through a technical example to solidify our understanding. Before we start, lets create a table. The following query creates a professor table and populates it with some records.

CREATE TABLE professor (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO professor VALUES (1, 'Dr. Hari');
INSERT INTO professor VALUES (2, 'Shyam');
INSERT INTO professor VALUES (3, 'Mukesh');
INSERT INTO professor VALUES (4, 'Aswat');

Output:

Professor data

Let’s create a student table as well. The following query creates the student table:

CREATE TABLE student (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50),
  profId INTEGER
);

Now suppose that in the college every student can be mentored by at most one professor. So the profId field in the above table should be unique for each record. But the student can no professor allotted to him at all. So the profId field can be NULL. Hence, the profId column should have only unique values but it can have NULL values. So this is just similar to creating a unique constraint on the profId column which will allow NULL values.

Before we start let’s run the following statement:

SET QUOTED_IDENTIFIER ON;

Now lets create the UNIQUE constraint.

CREATE UNIQUE NONCLUSTERED INDEX idx_profId_notnull
ON student(profId)
WHERE profId IS NOT NULL;

Explanation: As before we made use of NONCLUSTERED INDEX and WHERE clause to create the constraint on the profId column in the student table.

Now lets insert few records in the table.

INSERT INTO student VALUES (1, 'Aayush', 3);
INSERT INTO student VALUES (2, 'Akash', NULL);
INSERT INTO student VALUES (3, 'Dhruv', NULL);
INSERT INTO student VALUES (4, 'Riya', 1);
INSERT INTO student VALUES (5, 'Sid', 4);
INSERT INTO student VALUES (6, 'Sakshi', NULL);

Output:

Student data

Explanation: As we can see that all the values in the profId column are unique but it still allows NULL values to be repeated across records.

Now if we try to insert the following record:

INSERT INTO student VALUES (7, 'Ankit', 1);

Output:

Cannot insert duplicate key row in object 'dbo.student' with unique index 'idx_profId_notnull'.

Explanation: As we tried to insert value 1 in profId which is already present in the table, we received the above error.

Conclusion

In this article, we saw how using the UNIQUE constraint we can restrict the column to only have unique values. However, we also saw that the UNIQUE constraint doesn’t even allow multiple NULL values. We later saw how we can counter this using NONCLUSTERED INDEX and WHERE clause to only add the unique constraint to work with only non-null values. Do keep in mind for the above solution to work we need to set QUOTED_IDENTIFIER to be ON. We also later looked at a technical example to enhance our understanding.