UNIQUE Constraint FAQS

1. What is a UNIQUE constraint in Oracle?

A UNIQUE constraint ensures that no two rows contain duplicate values in a column or combination of columns.

Example:

CREATE TABLE employees (

   email VARCHAR2(100) UNIQUE

);

Each email value must be unique.

2. What problem does UNIQUE constraint solve?

Prevents:

• Duplicate business keys (email, username, PAN)
• Data inconsistency
• Multiple records for same entity

Used for:

• Login IDs
• Account numbers
• Natural business keys

3. How is UNIQUE different from PRIMARY KEY?

Feature	UNIQUE	PRIMARY KEY
Prevents duplicates	Yes	Yes
Allows NULL	Yes (one NULL allowed)	No
Number per table	Multiple	Only one
Creates index	Yes	Yes
Identifies row uniquely	Not necessarily	Yes

• PRIMARY KEY automatically includes NOT NULL
• UNIQUE allows one NULL unless combined with NOT NULL

4. How does UNIQUE work internally?

• Oracle creates a unique index automatically
• Index enforces uniqueness
• Checked during INSERT/UPDATE

Example:

CREATE TABLE employees (

   email VARCHAR2(100),

   CONSTRAINT emp_email_uk UNIQUE (email)

);

5. Can UNIQUE be defined on multiple columns?

Yes — composite UNIQUE constraint.

CREATE TABLE users (

   first_name VARCHAR2(50),

   last_name  VARCHAR2(50),

   CONSTRAINT users_name_uk UNIQUE (first_name, last_name)

);

Combination must be unique; individual columns can have duplicates.

6. Does UNIQUE allow NULL values?

Yes, one NULL per column is allowed.

To prevent NULL:

email VARCHAR2(100) NOT NULL UNIQUE

7. What happens when UNIQUE is violated?

Oracle raises:

ORA-00001: unique constraint violated

Second insert of a duplicate fails.

8. Can you add UNIQUE to an existing table?

ALTER TABLE employees

ADD CONSTRAINT emp_email_uk UNIQUE (email);

• Existing data must have no duplicates.

9. Can UNIQUE be dropped?

ALTER TABLE employees

DROP CONSTRAINT emp_email_uk;

Removes the constraint and associated unique index if auto-created.

10. Does UNIQUE affect performance?

Positive: Improves queries, helps optimizer, improves joins
Negative: Slight overhead on INSERT/UPDATE, index maintenance

Overall, benefits usually outweigh overhead.

11. Should foreign keys be UNIQUE?

No. Many child rows can reference one parent; indexing is recommended, not UNIQUE.

12. Can UNIQUE be DEFERRABLE?

Yes — validation can be delayed until COMMIT.

ALTER TABLE employees

ADD CONSTRAINT emp_email_uk UNIQUE (email)

DEFERRABLE INITIALLY DEFERRED;

Useful for batch operations with temporary duplicates.

13. How to check UNIQUE constraints?

SELECT constraint_name, constraint_type

FROM user_constraints

WHERE table_name = 'EMPLOYEES';

Constraint type U → UNIQUE

Columns:

SELECT column_name

FROM user_cons_columns

WHERE constraint_name = 'EMP_EMAIL_UK';

14. UNIQUE vs Unique Index

Feature	UNIQUE Constraint	Unique Index
Enforces rule	Yes	Yes
Logical rule	Yes	No
Metadata in constraints view	Yes	No
Best practice for integrity	Yes	Not recommended alone

Use UNIQUE constraint to clearly define business rules.

15. Common mistakes

• Adding constraint without checking duplicates
• Assuming UNIQUE prevents NULL
• Redundant UNIQUE constraint and index
• Not indexing composite foreign keys properly

16. Best Practices

• Use for natural business keys
• Combine with NOT NULL if required
• Meaningful names
• Avoid unnecessary composite UNIQUE constraints
• Use DEFERRABLE only when needed
• Let Oracle auto-create index unless specific design is needed

17. Real-world Example

Bad design:

CREATE TABLE users (

   username VARCHAR2(50)

);

Better design:

CREATE TABLE users (

   user_id NUMBER PRIMARY KEY,

   username VARCHAR2(50) NOT NULL UNIQUE

);

Prevents duplicates and missing values.

18. Interview Tip

“UNIQUE constraint ensures no duplicate values exist in a column or combination of columns. It automatically creates a unique index, allows one NULL, and is used for business keys like email or username.”