Oracle DB Unique Index

Database performance is one of the essential roles of a database developer. Ensuring your database is running at optimum performance can significantly impact the applications reading or writing to that database.

Although numerous ways of improving database performance exist, one feature is almost universal to any database. Database indexes are data structures or objects used to enhance the speed of data retrieval from the table.

When used correctly, database indexes can reduce the speed of a query by nearly half, depending on the target data, layout, available resources, etc.

Oracle Unique Index

We can use a unique index to ensure that no duplicate rows are stored in a given column. If the column of a given index contains a unique rule, attempting two add two rows with a similar value in that column will result in an error indicating a unique constraint violation.

In Oracle, we can create a unique index using the CREATE UNIQUE INDEX statement as shown:

CREATE UNIQUE INDEX index_name ON table_name(columns);

The columns included in the index will not accept any duplicate rows.

Example Unique Table Illustration

To demonstrate how to create and use a unique index, take the table shown below:

Oracle Create Unique Index on the First_Name Column

The following example statement shows how to create a unique index using the first_name column.

CREATE UNIQUE INDEX first_name_unique ON sample_data (first_name);

By enabling this index, we cannot insert more than one row with the same first name.

Take, for example, the insert statement below:

INSERT INTO sample_data (id, first_name, ip_address, btc_address, credit_card, identifier)
		values(11, 'Wallas', '169.158.70.77', '1CNz5d1d5SC8SaR6dFSVihwztqYx5Fg77q', '4017955174552', '26811d77-0a3a-4397-bc33-f7835f7c7ab9');

If we run the insert statement above, we should get an error as shown:

[23000][1] ORA-00001: unique constraint (HR.FIRST_NAME_UNIQUE) violated

As we can see, inserting the above value violates the unique constraint for the first_name column.

Oracle Create Unique Index with Two Columns

We can also have a unique index comprised of more than one column. In the example below, we create a unique index using the first_name and io_address columns.

CREATE UNIQUE INDEX verify_columns ON sample_data (first_name, ip_address);

Similarly, adding duplicate values for either the first_name or ip_address column will result in unique index violation errors.

Oracle Automatic Unique Indexes

Have you ever wondered what happens when you declare a table column with a primary key or unique constraint?

In simple terms, if you set a column as the table's primary key or assign a unique constraint to a given column, the database engine automatically creates a unique index for that column.

This ensures that no duplicate value is inserted in that column.

Take, for example, the statement below:

CREATE TABLE sample_data (
	id number,
	first_name varchar2 (50),
	ip_address varchar2 (20),
	btc_address varchar2 (50),
	credit_card varchar2 (50),
	identifier varchar2 (40),
	CONSTRAINT sample_pk PRIMARY KEY (id)
);

In the example above, we create a table and set the id column as the table's primary key. Then, to view the unique constraint associated with that column (automatically generated), we can run the command:

SELECT
	index_name,
	index_type,
	visibility,
	status,
	TABLE_NAME
FROM
	all_indexes
WHERE
	TABLE_NAME = 'SAMPLE_DATA';

In this case, we can see the unique index created by the database engine for the id column.

Closing

In this guide, you learned how to create and use unique indexes in the Oracle database. You also learned what happens when you assign a primary key or unique constraint to a table column.