Using Unique Constraints in Oracle

This article is written about how to create, drop, disable, and allow unique constraints in Oracle with syntax and examples.

What is a unique constraint in Oracle?

A unique constraint is a single subject or mixture of fields that uniquely defines a record. Some of the fields can include null values as long as the aggregate of values is unique.

Note

In Oracle, a special constraint can not incorporate more than 32 columns. A unique constraint can be described in either a CREATE TABLE announcement or an ALTER TABLE statement.

What is the difference between a special constraint and a most important key?

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

Oracle does now not allow you to create each a principal key and unique constraint with the same columns.

Create special Contraint – Using a CREATE TABLE statement

The syntax for creating a unique constraint the use of 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 desk that you desire 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 special constraint on the dealer desk referred to as supplier_unique. It consists of only one subject – the supplier_id field.

We may want to also create a special constraint with extra than one area as in the instance 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 declaration

The syntax for growing a unique constraint the usage of an ALTER TABLE statement in Oracle is:

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

table_name The title of the table to modify. This is the desk that you wish to add a unique constraint to. constraint_name The identify of the unique constraint. column1, column2, … column_n The columns that make up the unique constraint.

Example

Let’s look at an instance of how to add a unique constraint to an current desk in Oracle the usage of the ALTER TABLE statement.

ALTER TABLE supplier
ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);

In this example, we’ve got created a unique constraint on the current supplier desk called supplier_unique. It consists of the subject referred to as supplier_id.

We should additionally create a unique constraint with extra than one subject as in the example below:

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

Drop Unique Constraint

The syntax for shedding a unique constraint in Oracle is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

table_name The identify of the table to modify. This is the table that you wish to remove the special constraint from. constraint_name The title of the special constraint to remove.

Example

Let’s look at an example of how to dispose of a special constraint from a desk in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_unique;

In this example, we’re shedding a special constraint on the provider desk 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 title of the desk to modify. This is the table whose special constraint you desire to disable. constraint_name The title of the special constraint to disable.

Example

Let’s look at an instance of how to disable a unique constraint in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_unique;

In this example, we’re disabling a special constraint on the supplier table known as 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 desk to modify. This is the table whose special constraint you wish to enable. constraint_name The title of the special constraint to enable.

Example

Let’s appear at an instance of how to allow a unique constraint in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_unique;

In this example, we’re enabling a special constraint on the provider desk known as supplier_unique.