Home » Oracle SQL » Oracle UNIQUE KEY with Examples

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:

SQL

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:

SQL

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:

SQL

Add UNIQUE contraint – Using an ALTER TABLE statement

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

Syntax:

SQL

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.

SQL

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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.