• September 29, 2014
  • SQL
  • Comments Off on SQL UNIQUE Constraint

SQL UNIQUE Constraint

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:

How to build java project using ant in eclipse?
JAXB marshalling – convert java object to xml example.
How to create pdf file in java using iText jar?
Generics class example.
OGNL in struts 2.
Hibernate One-to-One Mapping using xml.
Send inline image in email using JavaMail API.
Quartz 2 JobListener example.

 


Comments are closed.