Oracle Unique Index FAQs with examples

1. What is a Unique Index in Oracle?

A Unique Index ensures that all non-NULL values in the indexed column, or combination of columns, are unique. It prevents duplicate indexed values from being inserted.

Example:

CREATE UNIQUE INDEX idx_emp_email
ON employees(email);

2. Why do we use a Unique Index?

Unique indexes are used to:

• Prevent duplicate values
• Enforce uniqueness
• Improve query performance
• Support PRIMARY KEY and UNIQUE constraints

3. How do you create a Unique Index?

Syntax:

CREATE UNIQUE INDEX index_name
ON table_name(column_name);

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

4. What happens if duplicate values are inserted?

Oracle returns an error and does not allow the duplicate value.

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

INSERT INTO employees
VALUES (101, 'John', 'john@gmail.com');

INSERT INTO employees
VALUES (102, 'David', 'john@gmail.com');

Output:

ORA-00001: unique constraint violated

The second INSERT fails because john@gmail.com already exists.

5. Can a Unique Index contain NULL values?

Yes. A single-column Oracle B-Tree unique index can allow multiple NULL values. Oracle does not store an index entry when all indexed columns are NULL.

Example:

CREATE UNIQUE INDEX idx_phone
ON employees(phone_number);

These inserts are allowed:

INSERT INTO employees(employee_id, phone_number)
VALUES (1, NULL);

INSERT INTO employees(employee_id, phone_number)
VALUES (2, NULL);

Important:
A Unique Index and a PRIMARY KEY are different. A PRIMARY KEY does not allow NULL values, while a standalone unique index can allow NULLs.

6. Can a Unique Index be created on multiple columns?

Yes. This is called a Composite Unique Index.

CREATE UNIQUE INDEX idx_email_dept
ON employees(email, department_id);

The combination of email and department_id must be unique for non-NULL indexed values.

7. What is the difference between a Unique Index and a Normal Index?

```

Unique Index	Normal Index
Prevents duplicate indexed values	Allows duplicate values
Enforces uniqueness	Does not enforce uniqueness
Can support unique constraints	Used mainly for faster data access
Can improve query performance	Can improve query performance

8. What is the difference between a Primary Key and a Unique Index?

```

Primary Key	Unique Index
Does not allow NULL values	Can allow NULL values
Only one primary key per table	Multiple unique indexes are allowed
A database constraint	A database index
Enforces entity integrity	Enforces uniqueness of indexed values

9. Does a Unique Constraint create a Unique Index?

Oracle normally creates a unique index to enforce a UNIQUE constraint if a suitable index does not already exist.

Example:

ALTER TABLE employees
ADD CONSTRAINT uk_email
UNIQUE(email);

10. How do you view Unique Indexes?

SELECT index_name,
       uniqueness,
       table_name
FROM user_indexes
WHERE uniqueness = 'UNIQUE';

11. How do you drop a Unique Index?

DROP INDEX idx_email;

Note:
If the index is being used to enforce a PRIMARY KEY or UNIQUE constraint, normally manage or drop the constraint rather than attempting to drop its supporting index directly.

12. Can one table have multiple Unique Indexes?

Yes.

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

CREATE UNIQUE INDEX idx_aadhar
ON employees(aadhar_number);

CREATE UNIQUE INDEX idx_pan
ON employees(pan_number);

Each unique index independently enforces uniqueness for its indexed values.

13. What are the advantages of a Unique Index?

• Prevents duplicate data
• Improves query performance
• Helps enforce business rules
• Helps maintain data integrity
• Speeds up searches on unique columns

14. What are the disadvantages of a Unique Index?

• Uses additional storage space
• Adds overhead to INSERT operations
• Adds overhead to UPDATE operations on indexed columns
• Requires index maintenance during DML operations

15. Unique Index Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  email       VARCHAR2(100)
);

Create a Unique Index:

CREATE UNIQUE INDEX idx_email
ON employees(email);

Insert the first row:

INSERT INTO employees
VALUES (1, 'john@gmail.com');

Try to insert the same email again:

INSERT INTO employees
VALUES (2, 'john@gmail.com');

Output:

ORA-00001: unique constraint violated

The second row is rejected because the email value already exists in the unique index.

16. Real-World Example

Suppose every employee must have a unique company email.

Create the index:

CREATE UNIQUE INDEX idx_company_email
ON employees(company_email);

Now Oracle prevents duplicate non-NULL company email addresses, ensuring that the same email cannot be assigned to multiple employees.

Key Point:
A Unique Index combines fast indexed access with uniqueness enforcement. It is useful for columns such as email addresses, employee identifiers, account numbers, and other values that should not be duplicated.