SQL UNIQUE Constraint

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in the database table.

UNIQUE and PRIMARY KEY constraints provide a uniqueness guarantee for columns or column sets.

PRIMARY KEY has an automatically defined UNIQUE constraint.

Please note that each table can have multiple UNIQUE constraints, but each table can only have one PRIMARY KEY constraint.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "Id_P" column when the "Persons" table is created:

MySQL:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

If you need to name the UNIQUE constraint and define a UNIQUE constraint on multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

SQL UNIQUE Constraint on ALTER TABLE

When the table has been created, if you need to create a UNIQUE constraint on the 'Id_P' column, please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD UNIQUE (Id_P)

If you need to name the UNIQUE constraint and define a UNIQUE constraint on multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

Revoke UNIQUE Constraint

To revoke the UNIQUE constraint, please use the following SQL:

MySQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID