Unique Index FAQS

1. What is a Unique Index in Oracle?

A Unique Index ensures that no two rows contain duplicate values in the indexed column(s).

It enforces uniqueness at the database level.

Example:

CREATE UNIQUE INDEX emp_email_uk_idx

ON employees(email);

Now, duplicate email values are not allowed.

2. How is Unique Index different from Normal (Non-Unique) Index?

Feature	Unique Index	Non-Unique Index
Allows duplicate values	No	Yes
Enforces data integrity	Yes	No
Automatically created by PK	Yes	No
Performance difference	Minimal	Minimal

Main difference: data validation, not speed.

3. What happens internally when inserting duplicate values?

Example:

INSERT INTO employees(email)

VALUES ('john@email.com');

If 'john@email.com' already exists, Oracle raises:

ORA-00001: unique constraint violated

Because:

·        Unique index checks leaf block

·        Finds existing value

·        Rejects insertion

4. Is Unique Index automatically created for Primary Key?

Yes.

When you create:

ALTER TABLE employees

ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

Oracle automatically creates a unique index (if one doesn’t already exist).

5. Does Unique Constraint always create Unique Index?

Yes (unless you specify USING INDEX and reuse an existing index).

Constraint enforces rule.
Index provides physical mechanism.

6. Can Unique Index be created on multiple columns?

Yes. That is called a Composite Unique Index.

Example:

CREATE UNIQUE INDEX emp_dept_email_uk_idx

ON employees(department_id, email);

This means:

·        Combination must be unique

·        Individual columns may repeat

Valid:

dept_id	email
10	john
20	john

Invalid:

dept_id	email
10	john
10	john

7. Can Unique Index allow NULL values?

Yes.

Important rule:

Oracle allows multiple NULL values in a Unique Index.

Example:

CREATE UNIQUE INDEX emp_comm_uk_idx

ON employees(commission_pct);

If commission_pct is NULL:

·        Multiple NULL rows allowed

·        Because NULL ≠ NULL in Oracle

8. What type of index is Unique Index internally?

It is still a B-Tree index.

Only difference:

·        Enforces uniqueness during insert/update

9. How does Unique Index affect performance?

SELECT performance

Same as B-Tree index.

INSERT performance

Slight overhead:

·        Must check if value already exists

UPDATE performance

If indexed column updated:

·        Remove old entry

·        Insert new entry

·        Validate uniqueness

DELETE performance

Removes entry from index.

10. What is Unique Index Scan?

When searching for exact match on unique index:

SELECT * FROM employees WHERE employee_id = 100;

Oracle performs:

INDEX UNIQUE SCAN

This is the fastest index scan type.

Because:

·        At most one row exists

11. When should you create a Unique Index manually?

Use cases:

·        Enforce business rules (email must be unique)

·        Prevent duplicate data

·        Improve lookup performance on unique column

·        When not using primary key but still need uniqueness

12. What are disadvantages?

·        Slight overhead on DML

·        Additional storage space

·        Can slow bulk inserts

·        Must maintain during updates

But benefits usually outweigh cost.

13. Unique Index vs Primary Key

Feature	Unique Index	Primary Key
Allows NULL	Yes	No
Only one per table	No	Yes
Enforces entity integrity	No	Yes
Backed by unique index	Yes	Yes

Primary Key = Unique + NOT NULL.

14. Can you disable Unique Index?

You can:

ALTER INDEX emp_email_uk_idx UNUSABLE;

But:

·        Constraint enforcement stops

·        Inserts may fail if constraint exists

Better approach:

Disable constraint, not index.

15. Can Unique Index be Function-Based?

Yes.

Example:

CREATE UNIQUE INDEX emp_upper_email_uk_idx

ON employees(UPPER(email));

Prevents:

·        'John@email.com'

·        'john@email.com'

from being treated differently.

16. What is Deferrable Unique Constraint?

You can create:

ALTER TABLE employees

ADD CONSTRAINT emp_email_uk

UNIQUE (email)

DEFERRABLE INITIALLY DEFERRED;

Validation happens at:

·        Commit time

·        Not immediately

Useful for batch operations.

17. What happens during bulk insert?

Unique index:

·        Checks each inserted row

·        Can slow down large batch loads

Solution:

·        Drop index

·        Load data

·        Recreate index

(Used in Data Warehouse)

18. Real-world Example

Requirement:

“No two users can register with same username.”

Solution:

CREATE UNIQUE INDEX users_username_uk

ON users(username);

Now database prevents duplicates automatically.

19. Performance best practices

·        Use unique index for lookup columns

·        Use for primary and alternate keys

·        Avoid unnecessary unique indexes

·        Keep statistics updated

·        Avoid frequent updates on unique columns

·        Use function-based unique index for case-insensitive uniqueness

20. Interview Tip

If asked:

“What is a Unique Index and how is it different from Primary Key?”

Answer:

“A Unique Index ensures that no duplicate values exist in the indexed column(s). It is implemented as a B-Tree index and enforces uniqueness during insert and update operations. A Primary Key is a special type of unique constraint that also enforces NOT NULL and allows only one per table. Both are backed by unique indexes.”