Unique Index in Oracle with Examples

In this tutorial, we will learn how to create unique index on a table which prevents duplicate values within the indexed column.

We can create unique index on one or more columns and technically, its same as creating a primary key or unique constraint as both serve the same purpose of preventing duplicate values in rows in an indexed column.

Syntax:

The syntax for creating Unique Index is as follows:

CREATE UNIQUE INDEX index_name 
ON table_name(column1, column2)...

Here,

index_name – It is the name of index

table_name – It is the name of table on which we want to create unique index.

column1, column2 .. – We can create unique index one or more columns. Unique Index with more than one columns is known as Unique Index.

The important thing to keep in mind is that Unique Index allows NULL values in columns.

Let’s see the following example to understand this better:

Oracle Unique Index Example

We will be using the interns_table that we previously created for this example:

Oracle Unique Index on One Column

The following query creates index on intern_id column in intern table:

CREATE UNIQUE INDEX u_idx ON test_table(intern_id);

Result:

Once the index has been created we cannot add duplicate values to this column.

Let’s try to insert one duplicate value in intern_id column using this query:

INSERT INTO intern_table
VALUES(107, 'Dupli','Kate',5000,200, 'F', 'Y');

Result:

Unique Index restricts duplicate values from entering into indexed column by giving following error:

Error report -
ORA-00001: unique constraint (HR.U_IDX) violated

Oracle Unique Index on more than one Columns

The following query will create unique index on first_name and last_name column in interns table:

CREATE UNIQUE INDEX uniq_idx ON intern_table(first_name,
last_name);

Result:

Unique Index on two columns will check for uniqueness based on combination of two columns. If we see that intern_table correctly, there are two instances of ‘Walter’ in first_name column and two instances of ‘White’ in last name, yet Oracle did not return any error. This happens because the values in both columns are unique in combination.

Data Dictionary View for Unique Index

The following queries give us important information about the Unique indexes present in Oracle database. We can access this information using data dictionary views.

This query will show us all the indexes that are associated with intern_table.

CREATE UNIQUE INDEX unq_idx ON test_table(first_name,
last_name);
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND lower(ic.table_name) = 'intern_table';

Result:

We can see our composite unique index( UNIQ_IDX) associated with two columns and we can see the column position as 1 or 2 indicating that same unique index applies on two columns.

Drop Unique Index

We use the following query to drop unique index from our table:

DROP INDEX unq_idx;

In this tutorial, we learned how to use and create Unique Index which is used for restricting duplicate values in columns.