Unique Constraint or Unique key in SQL Server enforces the uniqueness of the column value. i.e. no two rows of a table can have the same values. In this tutorial, we will learn how to create a Unique Constraint, how to drop it, how to add a Unique key to an existing table, etc.
Table of Contents
Need for Unique Constraint
The SQL Server Primary Key Constraint enforces the uniqueness of the column ( or group of column) values. But we can create only one Primary key per table.
In the following example, we make EmployeeID as Primary Key. But what if we want EmailID to be unique. This is where we use the Unique constraint.
CREATE TABLE Employee (
EmployeeID int ,
FirstName varchar(50) ,
LastName varchar(50) ,
Department varchar(20) ,
EmailID varchar(50) ,
Primary Key (EmployeeID)
)When we add a UNIQUE constraint to an existing column or columns in the table, the SQL Server ensures that all values in the column (or columns) are unique. It does so by creating a non clustered index on those rows.
Create Unique Constraint
CREATE TABLE Employee (
EmployeeID int ,
Name varchar(50) ,
EmailID varchar(50) UNIQUE ,
Primary Key (EmployeeID)
)
insert into Employee
values (1,'Olive Yew','[email protected]'),
(2,'Aida Bugg','[email protected]')
select * from Employee
*** Result ****
EmployeeID Name EmailID
---------- --------- ----------------
1 Olive Yew [email protected]
2 Aida Bugg [email protected]Now, when you try to insert a duplicate value into the EmailID column, the SQL Server throws a Violation of UNIQUE KEY constraint error.
insert into Employee values (3,'Olive Yew','[email protected]')Violation of UNIQUE KEY constraint UQ__Employee__7ED91AEE70A8B9AE. Cannot insert duplicate key in object dbo.Employee.
Composite Unique Key
You can also create a Composite Unique Key consisting of two or more fields. To do that we need to apply the Unique Constraint on the table level.
In the following example, we create a unique key consisting of FirstName & LastName.
CREATE TABLE TableB (
ID int ,
FirstName varchar(50) ,
LastName varchar(50) ,
Primary Key (ID),
UNIQUE (FirstName,LastName)
)
The FirstName & LastName themselves can contain duplicate values.
insert into TableB values(1,'Olive','Yew')
insert into TableB values(2,'Olive','Bugg') --ok
insert into TableB values(3,'Aida','Yew') --okBut, Both together should not contain any duplicate values
insert into TableB values(2,'Olive','Yew') --Error
--Violation of PRIMARY KEY constraint 'PK__TableB__3214EC271A9EF37A'.
--Cannot insert duplicate key in object 'dbo.TableB'The following example creates two separate Unique key constraints one for each FirstName & LastName.
CREATE TABLE TableB (
ID int ,
FirstName varchar(50) UNIQUE ,
LastName varchar(50) UNIQUE ,
Primary Key (ID),
)Naming the Unique Key
The following queries show how you can give a name to Unique Constraint.
CREATE TABLE TableB (
ID int ,
FirstName varchar(50) ,
LastName varchar(50) ,
Primary Key (ID),
CONSTRAINT UK_FirstName UNIQUE(FirstName)
)
--Composite Uniqu Key
CREATE TABLE TableC (
ID int ,
FirstName varchar(50) ,
LastName varchar(50) ,
Primary Key (ID),
CONSTRAINT UK_FirstName_LastName UNIQUE(FirstName, LastName)
)Finding the Unique Constraints in the Table
Use the following query to find out all the Unique Constraints with their names & column names.
CREATE TABLE TableB (
ID int ,
FirstName varchar(50) ,
LastName varchar(50) ,
Primary Key (ID),
CONSTRAINT UK_FirstName_LastName UNIQUE(FirstName,LastName)
)
select CCU.CONSTRAINT_NAME, CCU.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as CCU
on TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
and TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
and TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
where TC.CONSTRAINT_TYPE = 'UNIQUE'
and TC.TABLE_NAME = 'TableB' //change table name
***output ****
CONSTRAINT_NAME COLUMN_NAME
--------------- ---------------
UK_FirstName_LastName FirstName
UK_FirstName_LastName LastNameDropping the Unique Key
Dropping a Unique Key constraint is similar to dropping any other constraint. First, find out the name of the unique constant using the query from the previous section and use the Alter table Drop constraint query to delete the Unique key
Alter table TableB drop CONSTRAINT UK_FirstNameAdding Unique Constraint on Existing Table
The following query adds a Unique Constraint to an existing table.
The SQL Server will check the Uniqueness of existing values before creating the Unique Constraint. If the column to which we apply unique constraint has duplicate values, the SQL Server returns an error and does not add the constraint
CREATE TABLE TableB (
ID int ,
FirstName varchar(50) ,
LastName varchar(50) ,
Primary Key (ID),
)
//Adding the Unique Constraint
Alter Table TableB ADD CONSTRAINT UK_FirstName_LastName UNIQUE(FirstName,LastName)You can also create the constrain without naming it.
Alter Table TableB ADD UNIQUE(FirstName,LastName)Note that running the above query multiple times will result in multiple constraints on the same fields. That is the reason why you must name the constraint using a naming convention, which will prevent the duplication of constraints.
Rules & Limitations
Unique Key on Nullable columns allowed
UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column
You can insert a NULL value into the EmailID field
CREATE TABLE Employee (
EmployeeID int ,
Name varchar(50) ,
EmailID varchar(50) UNIQUE ,
Primary Key (EmployeeID)
)
insert into Employee
values (1,'Olive Yew','[email protected]'), --OK
(2,'Aida Bugg','[email protected]') --OK
-- OK One null value allowed in EmailID field
insert into Employee values (3,'Olive Yew',null)But inserting another NULL in EmaildID field will result Violation of UNIQUE KEY constraint error
insert into Employee values (4,'Allie Grater',null)
//Violation of UNIQUE KEY constraint 'UQ__Employee__7ED91AEE7755B73D'.
//Cannot insert duplicate key in object 'dbo.Employee'.You can use Unique Key in a Foreign Key constraint
A UNIQUE constraint can be referenced by a Foreign key constraint. The following example, EmailID field of tableA is set as Foreign Key referencing the EmailID of Employee Table
CREATE TABLE Employee (
EmployeeID int ,
Name varchar(50) ,
EmailID varchar(50) UNIQUE ,
Primary Key (EmployeeID)
)
CREATE TABLE TableA (
TableAID int ,
EmailID varchar(50) ,
Primary Key (TableAID) ,
FOREIGN KEY (EmailID) REFERENCES Employee(EmailID)
)Unique Key & Index
The SQL Server creates a NON CLUSTERED index to enforce the UNIQUE constraint. You can change that behavior and create the CLUSTERED index instead. Since a table can have only one CLUSTERED index, the primary key index will automatically become a NONCLUSTERED index
CREATE TABLE Employee (
EmployeeID int ,
Name varchar(50) ,
EmailID varchar(50) ,
CONSTRAINT PK_Employee Primary Key NONCLUSTERED (EmployeeID),
CONSTRAINT UK_EmailID UNIQUE CLUSTERED (EmailID)
)
