The UNIQUE constraint is used to uniquely identify each row in a table. It is like primary key but it can contain one null value and a table can have more than one UNIQUE constraint.
Syntax of UNIQUE Constraint on one column with CREATE TABLE statement:
MySQL:
CREATE TABLE Persons ( P_Id int NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25), Address varchar(255), UNIQUE (P_Id) ) |
SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, FirstName varchar(25) NOT NULL, LastName varchar(25), Address varchar(255), ) |
Syntax of UNIQUE Constraint on one column with ALTER TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD UNIQUE (P_Id) |
Syntax of UNIQUE Constraint on multiple columns with CREATE TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( P_Id int NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25), Address varchar(255), CONSTRAINT uc_PID UNIQUE (P_Id,FirstName) ) |
Syntax of UNIQUE Constraint on multiple columns with ALTER TABLE statement:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT uc_PID UNIQUE (P_Id,FirstName) |
Drop UNIQUE Constraint:
Use following syntax to drop the unique constraint.
MySQL:
ALTER TABLE Persons DROP INDEX uc_PID |
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT uc_PID |
Next Topic: SQL INDEX Constraint with example.
Previous Topic: SQL NOT NULL Constraint with example.
Related Topics: