SQL Server UNIQUE CONSTRAINT

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the UNIQUE CONSTRAINT to ensure the uniqueness of data in one or more columns in a table.

What is UNIQUE CONSTRAINT in SQL Server?

A UNIQUE CONSTRAINT as the name implies ensures uniqueness and can be defined on a column or a group of columns in a table. A column with the UNIQUE CONSTRAINT enabled on it cannot carry duplicate values and every value entered in the column must be unique so that no two records in the table has the same value for that column.

A UNIQUE CONSTRAINT is defined on columns which have the unique value requirement but which is not a primary key column or a column which is part of a multicolumnar primary key. Unlike the primary key however UNIQUE CONSTRAINT recognizes NULL value as a normal value and permits one NULL value for the column or columns with the UNIQUE CONSTRAINT enabled on them.

Operation

A UNIQUE CONSTRAINT is table specific and defined using the UNIQUE keyword while creating a table or later. The constraint can also be removed if not required.

SQL Server UNIQUE CONSTRAINT Syntax

The basic syntax of adding a UNIQUE CONSTRAINT using CREATE TABLE and ALTER TABLE statements and dropping a UNIQUE CONSTRAINT using ALTER TABLE statement is given below.

CREATE TABLE Syntax

Syntax without constraint name

The following CREATE table statements syntax do not use a constraint name. The first statement specifies the UNIQUE CONSTRAINT alongside the column and the second statement specifies the constraint at the end of the table after the column definitions. Both are valid.

CREATE TABLE table_name
(column1 DATATYPE,
column2 DATATYPE UNIQUE,
column3 DATATYPE UNIQUE,
………………………………);
CREATE TABLE table_name
(column1 DATATYPE,
column2 DATATYPE,
column3 DATATYPE,
………………………………
UNIQUE (column2, column3));

Syntax with constraint name

The following CREATE table statement defines a UNIQUE CONSTRAINT with constraint name. This is the best practice as having a constraint name helps in easy identification and modification of the constraint.

CREATE TABLE table_name
(column1 DATATYPE,
column2 DATATYPE,
column3 DATATYPE,
………………………………
CONSTRAINT constraint_name1
UNIQUE (column2, column3);

ALTER TABLE Syntax (to add constraint)

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column1, column2));

ALTER TABLE Syntax (to drop constraint)

ALTER TABLE tablename
DROP CONSTRAINT constraint_name;

In this syntax,

  • CREATE – clause used to create a database object (like table, index, schema etc.).
  • DATATYPE – keyword identifying the type of the data i.e. whether int, string, date etc.
  • UNIQUE – keyword to tell SQL that it should enforce uniqueness of value for the column.
  • CONSTRAINT – optional. Used to tell SQL that what follows is an integrity constraint.
  • constraint_name – optional. A name to identify the constraint that follows.
  • ALTER – clause used to modify a database object (like adding column to a table).
  • ADD – clause used to add something to a database object (like adding a column or constraint to a table).
  • DROP – clause used to remove something from a database object (like dropping columns or constraints from a table) or to delete a database object altogether (like dropping table).

SQL Server UNIQUE CONSTRAINT Examples

Let us understand the practical usage of the UNIQUE CONSTRAINT with the help of examples. We will create a sample table called customers containing customer information with the following query. The query adds a UNIQUE constraint for the customer phone number column cust_phone and allows NULL values for the column so that we can check out the NULL scenario.

CREATE TABLE customers
(cust_id INT IDENTITY(101,1) NOT NULL,
cust_name VARCHAR(50) NOT NULL,
cust_phone VARCHAR(15),
cust_email VARCHAR(50),
CONSTRAINT phone_different UNIQUE (cust_phone));

We can check the successful creation of the constraint with the help of the below query in SSMS (SQL Server Management Studio).

EXEC SP_HELP employees;

 It will show the constraint at the bottom.

SQL Server UNIQUE CONSTRAINT

1)  SQL Server UNIQUE CONSTRAINT – CREATE TABLE example

With the constraint in place we will add a record to the table with the following query.

INSERT INTO customers
(cust_name, cust_phone, cust_email)
VALUES ('Kevin John', '+16017715919', 'kev_rev_01@gmail.com');

  After adding the first record the table looks like below.

cust_idcust_namecust_phonecust_email
101Kevin John16017715919kev_rev_01@gmail.com

Now with the below query we will try to add another record with the same phone number for cust_phone. We can see the query fails with constraint violation error citing duplicate key value.

INSERT INTO customers
(cust_name, cust_phone, cust_email)
VALUES ('Norman Bates', '+16017715919', 'n.bates@gmail.com');

Msg 2627, Level 14, State 1, Server TEST-DBSERVER, Line 1
Violation of UNIQUE KEY constraint 'phone_different'. Cannot insert duplicate key in object 'dbo.customers'. The duplicate key value is (+16017715919).
The statement has been terminated.

To successfully add the record, we run the below query altering the phone number slightly so that we have 2 records in the table.

INSERT INTO customers
(cust_name, cust_phone, cust_email)
VALUES ('Norman Bates', '+16117715919', 'n.bates@gmail.com');
cust_idcust_namecust_phonecust_email
101Kevin John16017715919kev_rev_01@gmail.com
103Norman Bates16117715919n.bates@gmail.com

2)  SQL Server UNIQUE CONSTRAINT – ALTER TABLE examples

We will now drop the existing constraint with the following ALTER query.

ALTER TABLE employees
DROP CONSTRAINT phone_different;

After dropping the constraint, we will add it back. But this time we have added the constraint on 2 columns – cust_phone and cust_email with the following query.

ALTER TABLE customers
ADD CONSTRAINT different_phone_email
UNIQUE (cust_phone, cust_email);

With this new table constraint in place we will try to add one more customer record which duplicates the value in the cust_email column. The query fails with a constraint violation error citing duplicate key value as can be seen below.

INSERT INTO customers
(cust_name, cust_phone, cust_email)
VALUES ('Nancy Bates', '+16771521901', 'n.bates@gmail.com');

Msg 2627, Level 14, State 1, Server TEST-DBSERVER, Line 1
Violation of UNIQUE KEY constraint 'different_email'. Cannot insert duplicate key in object 'dbo.customers'. The duplicate key value is (n.bates@gmail.com).
The statement has been terminated.

3)  SQL Server UNIQUE CONSTRAINT – NULL value examples

We will now check the NULL value case by trying to insert a record with NULL value for cust_phone and cust_email with the following query. The query will run successfully and add the record to the table.

INSERT INTO customers
(cust_name, cust_phone, cust_email)
VALUES ('Nancy Bates', NULL, NULL);

After insertion the table will have the following records.

cust_idcust_namecust_phonecust_email
101Kevin John16017715919kev_rev_01@gmail.com
103Norman Bates16117715919n.bates@gmail.com
105Nancy BatesNULLNULL

Now we will try to add another record with NULL values for cust_phone and cust_email with the following query. The query will fail with the following constraint violation error citing duplicate key value. Thus, we see that only one NULL value can be accommodated by a column with the UNIQUE CONSTRAINT enabled.

INSERT INTO customers
(cust_name, cust_phone, cust_email)
VALUES ('Joshua Smith', NULL, NULL);

Msg 2627, Level 14, State 1, Server TEST-DBSERVER, Line 1
Violation of UNIQUE KEY constraint 'different_email'. Cannot insert duplicate key in object 'dbo.customers'. The duplicate key value is ().
The statement has been terminated.

Advertisement