WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL UNIQUE Constraint

SQL UNIQUE constraint

UNIQUE constraint uniquely identifies each record in a database table.

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

PRIMARY KEY constraints have automatically defined UNIQUE constraints.

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


SQL UNIQUE constraint on 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 a UNIQUE constraint 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 constraint when ALTER TABLE

To create a UNIQUE constraint on the "P_Id" column when the table has been created, use the following SQL:

MySQL /SQL Server /Oracle /MS Access:

ALTER TABLE Persons
ADD UNIQUE (P_Id)

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

MySQL /SQL Server /Oracle /MS Access:

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


Release UNIQUE constraint

To revoke 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