totn Oracle / PLSQL

Oracle / PLSQL: Unique Constraints

This Oracle tutorial explains how to create, drop, disable, and enable unique constraints in Oracle with syntax and examples.

What is a unique constraint in Oracle?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

Note

  • In Oracle, a unique constraint can not contain more than 32 columns.
  • A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

What is the difference between a unique constraint and a primary key?

Primary Key Unique Constraint
None of the fields that are part of the primary key can contain a null value. Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Oracle does not permit you to create both a primary key and unique constraint with the same columns.

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in Oracle is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);
table_name
The name of the table that you wish to create.
column1, column2
The columns that you wish to create in the table.
constraint_name
The name of the unique constraint.
uc_col1, uc_col2, ... uc_col_n
The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in Oracle using the CREATE TABLE statement.

CREATE TABLE supplier
( supplier_id numeric(10) NOT NULL,
  supplier_name varchar2(50) NOT NULL,
  contact_name varchar2(50),
  CONSTRAINT supplier_unique UNIQUE (supplier_id)
);

In this example, we've created a unique constraint on the supplier table called supplier_unique. It consists of only one field - the supplier_id field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) NOT NULL,
  supplier_name varchar2(50) NOT NULL,
  contact_name varchar2(50),
  CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name)
);

Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in Oracle is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
table_name
The name of the table to modify. This is the table that you wish to add a unique constraint to.
constraint_name
The name of the unique constraint.
column1, column2, ... column_n
The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in Oracle using the ALTER TABLE statement.

ALTER TABLE supplier
ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);

In this example, we've created a unique constraint on the existing supplier table called supplier_unique. It consists of the field called supplier_id.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE supplier
ADD CONSTRAINT supplier_name_unique UNIQUE (supplier_id, supplier_name);

Drop Unique Constraint

The syntax for dropping a unique constraint in Oracle is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
table_name
The name of the table to modify. This is the table that you wish to remove the unique constraint from.
constraint_name
The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_unique;

In this example, we're dropping a unique constraint on the supplier table called supplier_unique.

Disable Unique Constraint

The syntax for disabling a unique constraint in Oracle is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
table_name
The name of the table to modify. This is the table whose unique constraint you wish to disable.
constraint_name
The name of the unique constraint to disable.

Example

Let's look at an example of how to disable a unique constraint in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_unique;

In this example, we're disabling a unique constraint on the supplier table called supplier_unique.

Enable Unique Constraint

The syntax for enabling a unique constraint in Oracle is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
table_name
The name of the table to modify. This is the table whose unique constraint you wish to enable.
constraint_name
The name of the unique constraint to enable.

Example

Let's look at an example of how to enable a unique constraint in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_unique;

In this example, we're enabling a unique constraint on the supplier table called supplier_unique.