The unique key is a set of columns that identify only unique values in a table.

It is same as Primary key but it can accept up to one null value and no duplicate values.

Unique Constraint is defined automatically when the primary key is defined.

There may be many unique key constraints but only one PRIMARY KEY for a table.

If you want to create a unique constraint on a column it is done using following syntax:

 

SQL Server / Oracle / MS Access:

CREATE TABLE Students
(
ID NOT NULLUNIQUE,
Name varchar(255),
City Varchar(255)
)

 

MySQL:

CREATE TABLE Students
(
ID NOT NULL,
Name varchar(255),
City varchar(255),
UNIQUE (ID)
)

 

To define unique constraint on multiple columns:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLEStudent
(
ID NOT NULL,
Name varchar(255) NOT NULL ,
City varchar(255),
CONSTRAINTunique_keyUNIQUE (ID, Name)
)

 

SQL UNIQUE constraint on ALTER TABLE

If you want to create a unique constraint on a table that is already created then you should use below syntax.

On One columns

ALTER TABLE Students
ADD UNIQUE (Name)

 

On Multiple columns

ALTER TABLE Students
ADD CONSTRAINGunique_keyUNIQUE (ID, Name)

 

How to DROP a Unique key constraint?

MySQL:

ALTER TABLE Students
DROP INDEXunique_key

 

SQL Server / Oracle / MS Access:

ALTER TABLE Students
DROP CONSTRAINTunique_key