Oracle Unique Constraints - SQLS*Plus

Home » SQL Server Blog » Oracle PL/SQL » Oracle unique constraints

Oracle unique constraints21 August 2020

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.

Table of contents

1. The difference between a unique constraint and a primary key
   1. Primary key
   2. Unique limitation
2. Create a unique constraint using the CREATE TABLE operator
   1. Syntax:
3. Creation of a unique restriction using the ALTER TABLE operator
   1. Syntax for creating a unique constraint using the ALTER TABLE operator in Oracle:
4. Remove unique restriction
   1. Syntax to remove a unique restriction in Oracle:
5. Disabling the unique constraint
   1. Syntax for disabling a unique restriction in Oracle:
6. Enabling unique constraint
   1. Syntax to enable a unique limitation in Oracle:
7. WHAT IS UNIQUE KEY CONSTRAINT IN ORACLE SQL?

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?

Tags: Oracle, Oracle Database, Oracle SQL, PL/SQL, PLSQL, SQL