Oracle unique constraints

In this post you will learn how to create, delete, disable, and enable unique constraints in Oracle with syntax and examples.

A unique constraint is a single field or a combination of fields that uniquely define a record. Some of the fields may contain zero values if the combination of values is unique.

• In Oracle, the unique restriction may not contain more than 32 columns.
• The unique restriction can be defined either in the CREATE TABLE operator or in the ALTER TABLE operator.

The difference between a unique constraint and a primary key

Primary key

The fields that are part of the primary key cannot contain a zero value.

Unique limitation

Fields that are part of a unique constraint may contain zero values if the combination of values is unique.

Oracle does not allow you to create both a primary key and a unique constraint with identical columns.

Create a unique constraint using the CREATE TABLE operator

Syntax:

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 is the name of the table you want to create.
• column1, column2 – the columns you want to create in the table.
• constraint_name – the name of a unique constraint.
• uc_col1, uc_col2, … uc_col_n – columns that make up the unique constraint.

Let’s consider an example of creating a unique limitation in Oracle using the CREATE TABLE operator.

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 created a unique supplier_unique restriction for the supplier table. It consists of only one field – the supplier_id field.

We could also create a unique limit with several fields, 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)
);

Creation of a unique restriction using the ALTER TABLE operator

Syntax for creating a unique constraint using the ALTER TABLE operator in Oracle:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

• table_name is the name of the table to change. This is the table to which you want to add a unique constraint.
• constraint_name is the name of the unique constraint.
• column1, column2, … column_n are the columns that make up the unique constraint.

Let’s consider an example of how to add a unique limitation to an existing table in Oracle using the ALTER TABLE operator.

ALTER TABLE supplier
ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);

In this example, we created a unique limitation for an existing supplier table called supplier_unique. It consists of a field named supplier_id.

We could also create a unique constraint with multiple fields, as in the example below:

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

Remove unique restriction

Syntax to remove a unique restriction in Oracle:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

• table_name is the name of the table to change. This is the table from which you want to remove the unique constraint.
• constraint_name is the name of the unique constraint to be removed.

Let’s consider an example of how to remove a unique limitation from a table in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_unique;

In this example, we removed the unique supplier_unique restriction for the supplier table.

Disabling the unique constraint

Syntax for disabling a unique restriction in Oracle:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

• table_name is the name of the table to change. This is the table whose unique constraint you want to disable.
• constraint_name is the name of the unique constraint to disable.

Let’s consider an example of how to disable a unique limitation in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_unique;

In this example, we disabled the unique supplier_unique restriction for the supplier table.

Enabling unique constraint

Syntax to enable a unique limitation in Oracle:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

• table_name is the name of the table to change. This is the table whose unique constraint you want to enable.
• constraint_name is the name of the unique constraint to enable.

Let’s consider an example of how to enable a unique limitation in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_unique;

In this example, we enable a unique supplier_unique restriction for the supplier table.

WHAT IS UNIQUE KEY CONSTRAINT IN ORACLE SQL?