The UNIQUE constraint is used to uniquely identify each row in a table. It is like a primary key but it can contain one null value and a table can have more than one UNIQUE constraint.

Syntax of UNIQUE Constraint on one column with CREATE TABLE statement:

MySQL:
CREATE TABLE Persons
(
P_Id <strong>int</strong> NOT <strong>NULL</strong>,
FirstName varchar(25) NOT <strong>NULL</strong>,
LastName varchar(25),
Address varchar(255),
UNIQUE (P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id <strong>int</strong> NOT <strong>NULL</strong> UNIQUE,
FirstName varchar(25) NOT <strong>NULL</strong>,
LastName varchar(25),
Address varchar(255),
)

Syntax of UNIQUE Constraint on one column with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD UNIQUE (P_Id)

Syntax of UNIQUE Constraint on multiple columns with CREATE TABLE statement:

MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id <strong>int</strong> NOT <strong>NULL</strong>,
FirstName varchar(25) NOT <strong>NULL</strong>,
LastName varchar(25),
Address varchar(255),
CONSTRAINT uc_PID UNIQUE (P_Id,FirstName)
)

Syntax of UNIQUE Constraint on multiple columns with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT uc_PID UNIQUE (P_Id,FirstName)

Drop UNIQUE Constraint:

Use the following syntax to drop the unique constraint.

MySQL:
ALTER TABLE Persons DROP INDEX uc_PID
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT uc_PID