SQL UNIQUE

SQL UNIQUE constraint

UNIQUE constraint uniquely identifies each record in a database table.

UNIQUE and PRIMARY KEY constraints both provide uniqueness guarantees for columns or column sets.

PRIMARY KEY constraints have automatically defined UNIQUE constraints.

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

SQL UNIQUE constraints when CREATE TABLE

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

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 a UNIQUE constraint and define UNIQUE constraints for 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 constraints when ALTER TABLE

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

MySQL/SQL Server/Oracle/MS Access:

ALTER TABLE Persons
ADD UNIQUE (P_Id)

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

MySQL / SQL Server / Oracle / MS Access:

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

Cancel the UNIQUE constraint

If you need to cancel the UNIQUE constraint, please Use the following SQL: