tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities
Share this page:

Oracle/PLSQL: Unique Constraints

Learn 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 is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);

Example

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 in an ALTER TABLE statement is:

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

Example

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_unique UNIQUE (supplier_id, supplier_name);

Drop Unique Constraint

The syntax for dropping a unique constraint is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

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 is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

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 is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_unique;

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