Oracle UNIQUE KEY with Examples

When we apply UNIQUE constraint to a column(s) in table, Oracle ensures that each row within that column(s) is unique. However, NULL values are allowed for multiple rows as long as the combination is unique as well.

One of the difference between Primary key and Unique Constraint is that Primary Key constraint does not allow any NULL values in columns while a column(s) with UNIQUE constraint can contain NULL values as long the combination is unique.

In this tutorial, we will learn how to add, drop and enable/disable UNIQUE constraint in our table.

Add UNIQUE Constraint – Using a CREATE TABLE statement

Syntax:

CREATE TABLE table_name
(
  column1 data_type,
  column2 data_type,
  ...

  CONSTRAINT constraint_name UNIQUE (u_column1, u_column2 ...)
);

Here,

table_name – It is the name of table in which we want to add unique constraint.

column1, column2 – These are the name of columns which are present in the table.

constraint_name – This is the name that we give to the unique constraint.

u_column1, u_column2 – This is the column or columns on which we specifically a unique constraint.

Example:

Let’s create a table named Students and add a unique constraint on one of its columns using the following query:

CREATE TABLE students
( student_id NUMBER(10) NOT NULL,
  name VARCHAR2(50) NOT NULL,
  birth_date DATE NOT NULL,
  CONSTRAINT student_unique UNIQUE (student_id)
);

Suppose, we want the values in both ‘student_id’ and ‘name’ column in Students table to be Unique, then we add unique constraint to more than one columns at once using the following query:

CREATE TABLE students
( student_id NUMBER(10) NOT NULL,
  name VARCHAR2(50) NOT NULL,
  birth_date DATE NOT NULL,
  CONSTRAINT student_unique UNIQUE (student_id, name)
);

Add UNIQUE contraint – Using an ALTER TABLE statement

The syntax for adding a UNIQUE constraint to an existing table is as follows:

Syntax:

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

Here,

table_name- It is the name of table where we want to add UNIQUE constraint

constraint_name – Here, we give a unique name to this constraint

column1, column2 – This is the column(s) on which we want to apply UNIQUE constraint.

Example:

Let’s try to add a unique constraint to existing Students table using ALTER Statement.

ALTER TABLE students
ADD CONSTRAINT student_unique UNIQUE (student_id);

Similarly, we can add Unique constraint to multiple tables at once.

Drop Unique Constraint

If we want to drop the UNIQUE constraint then we use the following syntax:

Syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Here,

table_name – Name of table from which the UNIQUE constraint needs to be dropped

constraint_name – Name of constraint that we want to drop.

Example:

Let’s see one example where we try to drop the UNIQUE constraint from students table:

ALTER TABLE students
DROP CONSTRAINT student_unique;

Enable/Disable UNIQUE Constraint

Enable or Disable is used for Enforcing or Exempting a column from UNIQUE CONSTRAINT rules.

The syntax for disabling a unique constraint in Oracle is:

ALTER TABLE table_name
[ENABLE/DISABLE] CONSTRAINT constraint_name;

Here,

table_name – It’s the name of table in which we want to enable or disable UNIQUE constraint.

constraint_name – It is the name of constraint that we want to toggle as Enable or Disable.

Example:

Let’s see one example where we disable the previously created UNIQUE constraint in Students table:

ALTER TABLE students
DISABLE CONSTRAINT student_unique;

In this example, we disabled UNIQUE CONSTRAINT from Students table.

In this article, we learned how to add, drop and enable or disable UNIQUE constraint in Oracle.