Usage and examples of SQL UNIQUE keyword

SQL Keywords Reference

UNIQUE

UNIQUE constraint ensures that all values in a column are unique.

SQL constraint UNIQUE on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "ID" column when creating the table "Persons":

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
　　ID int NOT NULL UNIQUE,
　　LastName varchar(255) NOT NULL,
　　FirstName varchar(255),
　　Age int
);

MySQL:

CREATE TABLE Persons
(
　ID int NOT NULL,
　LastName varchar(255) NOT NULL,
　FirstName varchar(255),
　Age int,
　UNIQUE (ID)
);

To name a UNIQUE constraint and define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
　　ID int NOT NULL,
　　LastName varchar(255) NOT NULL,
　　FirstName varchar(255),
　　Age int,
　　CONSTRAINT UC_Person UNIQUE (ID, LastName)
);

SQL constraint UNIQUE on ALTER TABLE

To create a UNIQUE constraint on the 'ID' column after the table has been created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD UNIQUE (ID);

To name a UNIQUE constraint and define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

Delete UNIQUE Constraint

To delete a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP INDEX UC_Person;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

SQL Keywords Reference