Latest web development tutorials

SQL UNIQUE constraint

SQL UNIQUE constraint

UNIQUE constraint uniquely identifies each record in a database table.

UNIQUE and PRIMARY KEY constraints are column or set of columns provides a uniqueness guarantee.

PRIMARY KEY constraint automatically has a UNIQUE constraint defined.

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

CREATE TABLE SQL UNIQUE constraint when

The following SQL when the "Persons" table is created to create a UNIQUE constraint on the "P_Id" column:

MySQL:

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

SQL Server / Oracle / MS Access:

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

To name UNIQUE constraints UNIQUE constraints and define multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

SQL UNIQUE constraint ALTER TABLE when

When the table has been created, create a UNIQUE constraint in the column for "P_Id", please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD UNIQUE (P_Id)

To name UNIQUE constraints UNIQUE constraints and define multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

Undo UNIQUE constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID