Oracle UNIQUE Index

Summary: in this tutorial, you will learn how to use Oracle unique index to prevent duplicate values in the indexed column or columns of a table.

Introduction to Oracle UNIQUE index

An index can be unique or non-unique. A unique index ensures that no two rows of a table have duplicate values in the indexed column (or columns). A non-unique index does not impose this restriction on the indexed column’s values.

To create a unique index, you use the CREATE UNIQUE INDEX statement:

CREATE UNIQUE INDEX index_name ON 
table_name(column1[,column2,...]);
Code language: SQL (Structured Query Language) (sql)

In this syntax, UNIQUE is the only keyword that you need to add to the CREATE INDEX statement.

Oracle UNIQUE index on one column example

The following example creates a unique index on the email column of the members table:

CREATE UNIQUE INDEX members_email_i
ON members(email);
Code language: SQL (Structured Query Language) (sql)

By having this members_email_i index, you cannot have two rows with the same value in the email column.

The following statement will fail because it attempts to insert a new row whose email already exists:

INSERT INTO members(first_name, last_name, gender, dob, email)
VALUES('Pegpa','Elce','F',DATE '1990-01-02','[email protected]');
Code language: SQL (Structured Query Language) (sql)

Here is the error:

SQL Error: ORA-00001: unique constraint (OT.MEMBERS_EMAIL_I) violated
Code language: SQL (Structured Query Language) (sql)

Oracle UNIQUE index on two columns example

The following example creates a new table named unq_idx_demo with two columns a and b:

CREATE TABLE unq_idx_demo(
    a INT,
    b INT
);   
Code language: SQL (Structured Query Language) (sql)

To create a unique index on the two columns a and b, you use the following statement:

CREATE UNIQUE INDEX unq_idx_demo_ab_i
ON unq_idx_demo(a,b);
Code language: SQL (Structured Query Language) (sql)

The following statement inserts a new row into the unq_idx_demo table:

INSERT INTO unq_idx_demo(a,b)
VALUES(1,1);
Code language: SQL (Structured Query Language) (sql)

Because we have a unique index on the a and b columns, the combination of values in both columns are used for evaluating duplicate.

The following statement works because the pair (1,2) does not exist:

INSERT INTO unq_idx_demo(a,b)
VALUES(1,2);
Code language: SQL (Structured Query Language) (sql)

However, the following statement does not work because (1,1) already exists:

INSERT INTO unq_idx_demo(a,b)
VALUES(1,1);
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

SQL Error: ORA-00001: unique constraint (OT.UNQ_IDX_DEMO_AB_I) violated
Code language: SQL (Structured Query Language) (sql)

Oracle UNIQUE index, Primary Key constraint, and Unique constraint

When you define a PRIMARY KEY or a UNIQUE constraint for a table, Oracle automatically creates a unique index on the primary key or unique key columns to enforce the uniqueness.

The unique index associated with the constraint always has the name of the constraint, unless specify it explicitly otherwise.

The following statement creates a table named t1 with a primary key:

CREATE TABLE t1 (
    pk1 INT PRIMARY KEY,
    c1 INT
);
Code language: SQL (Structured Query Language) (sql)

To show the indexes of the t1 table, you use the following statement:

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status 
FROM 
    all_indexes
WHERE 
    table_name = 'T1';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

As can be seen clearly from the output, the SYS_C007876 unique index was created automatically with the generated name.

To specify the name for the primary key column, you use the UNIQUE index as shown in the following query:

CREATE TABLE t2 (
    pk2 INT PRIMARY KEY 
        USING INDEX (
            CREATE INDEX t1_pk1_i ON t2 (pk2)
    ),
    c2 INT
);
Code language: SQL (Structured Query Language) (sql)

In this example, we explicitly specified the name of the unique index.

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status 
FROM 
    all_indexes
WHERE 
    table_name = 'T2';
Code language: SQL (Structured Query Language) (sql)

The output is:

Instead of generating the index name, Oracle just used the one that we provided during table creation.

In this tutorial, you have learned how to use Oracle UNIQUE index to ensure the uniqueness of values in the indexed column or columns.