PRIMARY KEY FAQS

1. What is a PRIMARY KEY in Oracle?

A PRIMARY KEY uniquely identifies each row in a table.

• Does not allow NULL values
• Prevents duplicate values

Example:

CREATE TABLE employees (

   emp_id NUMBER PRIMARY KEY,

   name   VARCHAR2(100)

);

emp_id cannot be NULL or duplicated.

2. What rules does a PRIMARY KEY enforce?

Automatically enforces:

• NOT NULL
• UNIQUE

Oracle creates a unique index internally.

3. How many PRIMARY KEYs can a table have?

Only one PRIMARY KEY per table, which can be:

• Single-column
• Multi-column (composite)

4. What is a composite PRIMARY KEY?

A composite primary key has multiple columns.

Example:

CREATE TABLE order_items (

   order_id   NUMBER,

   product_id NUMBER,

   quantity   NUMBER,

   CONSTRAINT order_items_pk

   PRIMARY KEY (order_id, product_id)

);

Combination must be unique; individual columns may duplicate.

5. How does Oracle enforce PRIMARY KEY internally?

• Oracle creates a unique index automatically
• During INSERT/UPDATE, Oracle checks for duplicates and rejects NULLs

View index:

SELECT index_name

FROM user_indexes

WHERE table_name = 'EMPLOYEES';

6. What happens if PRIMARY KEY is violated?

Oracle raises:

• ORA-00001 → unique constraint violated
• ORA-01400 → cannot insert NULL

Example:

INSERT INTO employees VALUES (101, 'John');

INSERT INTO employees VALUES (101, 'Mike');

Second insert fails.

7. Can you add PRIMARY KEY to an existing table?

ALTER TABLE employees

ADD CONSTRAINT emp_pk PRIMARY KEY (emp_id);

Existing data must not contain duplicates or NULLs.

8. Can PRIMARY KEY be dropped?

ALTER TABLE employees

DROP CONSTRAINT emp_pk;

Drops the constraint and the automatically created index.

9. Difference between PRIMARY KEY and UNIQUE

Feature	PRIMARY KEY	UNIQUE
Allows NULL	No	Yes (one NULL)
Number per table	One	Multiple
Identifies entity	Yes	Not necessarily
Creates index	Yes	Yes

10. Should every table have a PRIMARY KEY?

Yes. Benefits:

• Ensures uniqueness
• Enables foreign key relationships
• Improves query performance and optimizer stats
• Prevents duplicate rows

11. Does PRIMARY KEY affect performance?

Positive: Faster joins, better optimizer statistics, efficient row lookup, enforces integrity

Slight overhead: Index maintenance during INSERT/UPDATE

Overall, benefits outweigh overhead.

12. Can PRIMARY KEY be DEFERRABLE?

Yes — validation can be delayed until COMMIT:

ALTER TABLE employees

ADD CONSTRAINT emp_pk PRIMARY KEY (emp_id)

DEFERRABLE INITIALLY DEFERRED;

Useful for temporary duplicates in transactions.

13. Surrogate key vs natural key

Natural Key: meaningful business data (email, SSN)
Surrogate Key: artificial numeric key (SEQUENCE or IDENTITY)

Best practice: surrogate key as PRIMARY KEY, natural key with UNIQUE.

14. Can PRIMARY KEY be on a partitioned table?

Yes. Must:

• Include partition key (local index)
• Or use a global index

Careful design is needed for large systems.

15. Common mistakes

• Not defining a primary key
• Using large VARCHAR columns
• Choosing frequently changing business key
• Not indexing foreign keys referencing PK
• Unnecessary composite PK

16. Best Practices

• Define one primary key per table
• Prefer numeric surrogate keys
• Keep it small and stable
• Avoid updating PK values
• Index referencing foreign keys
• Define during table creation

17. Real-world example

Bad design:

CREATE TABLE customers (

   name VARCHAR2(100),

   email VARCHAR2(100)

);

Better design:

CREATE TABLE customers (

   customer_id NUMBER PRIMARY KEY,

   name         VARCHAR2(100),

   email        VARCHAR2(100) UNIQUE

);

Ensures unique row identity and relational integrity.

18. Interview Tip

“A PRIMARY KEY uniquely identifies each row, enforces NOT NULL and UNIQUE, and automatically creates a unique index. Only one per table, single or composite. Essential for entity integrity and foreign key support.”