In case you want to keep the values unique in a column you have to define the column with UNIQUE constraint. Each newly inserted or updated value of a unique column must be different from the ones already in table column. In this section you will learn how to add unique constraint to table.

The unique constraint can be applied to single column or a group of columns. In case you are defining a set of columns as unique, the values stored in that group of columns must be unique as a unit. For example address as unique key can include columns house number, street, city, state and zip code. The unique constraint will restrict same address in more than one record. It means the combination of values for columns house no, street, city, state and zip code will be different for each row in a table with this set of columns as unique key.

How to Add UNIQUE Constraint to Table

Unique constraint can be added as column level or table level constraint.

Syntax for Column Level Unique Constraint

Column-name DATATYPE(size) [CONSTRAINT constraint-name] UNIQUE,

  • Column-Name is the name of the column that have to be unique at the time of data insertion or update
  • CONSTRAINT Constraint-name- the keyword and the name of the UNIQUE constraint (this is optional)
  • UNIQUE keyword

Column Level Unique Constraint without Constraint Name

CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustCity varchar2(30),
CustPhone varchar2(15)CONSTRAINT NN_custphone NOT NULL,
CustCreditScore number(4),
CustEmail varchar2(50) UNIQUE,
CONSTRAINT pk_custid PRIMARY KEY (CustID)
);

Column Level Unique Constraint with Constraint Name

CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustCity varchar2(30),
CustPhone varchar2(15)CONSTRAINT NN_custphone NOT NULL,
CustCreditScore number(4),
CustEmail varchar2(50) CONSTRAINT UK_CustEmail UNIQUE,
CONSTRAINT pk_custid PRIMARY KEY (CustID)
);

Syntax for Table Level Unique Constraint Simple/Composite

In this type the constraint is added at the end of table definition after the last column declaration. The table level unique constraint can be on single column or multiple columns.

(Table definition,
[CONSTRAINT constraint-name] UNIQUE (column-name 1, column-name 2…)

  • CONSTRAINT Constraint-name- the keyword and the name of the UNIQUE constraint (this is optional)
  • UNIQUE keyword
  • Column-Name(s) are the names of the columns that have to be unique at the time of data insertion or update

Table Level Unique Constraint on Multiple Columns

CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustHNO  varchar2(50),
CustStreet Varchar2(20),
CustCity varchar2(30),
CustPhone varchar2(15)CONSTRAINT NN_custphone NOT NULL,
CustCreditScore number(4),
CustEmail varchar2(50) CONSTRAINT UK_CustEmail UNIQUE,
CONSTRAINT pk_custid PRIMARY KEY (CustID),
CONSTRAINT UK_Address UNIQUE(CustHNO,CustStreet,CustCity)
);

Table Level Unique Constraint- 1  Multiple Columns Unique Constraint and 2 single column unique constraints

 

CREATE TABLE CustomerTbl
(
CustID number(4),
CustName varchar2(50),
CustHNO  varchar2(50),
CustStreet Varchar2(20),
CustCity varchar2(30),
CustPhone varchar2(15),
CustCreditScore number(4),
CustEmail varchar2(50),
CONSTRAINT pk_custid PRIMARY KEY (CustID),
CONSTRAINT UK_Address UNIQUE(CustHNO,CustStreet,CustCity),
CONSTRAINT UK_CustEmail UNIQUE (CustEmail),
CONSTRAINT UK_CustPhone UNIQUE (CustPhone)
);

Points to Remember

While understanding how to add UNIQUE constraint to table remember these points

  • You can create multiple Unique constraints in one table. All must be defined individually as shown in the last example.
  • A unique column can store a null value if no values are assigned while insertion.