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?

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.