FOREIGN KEY FAQS

1. What is a FOREIGN KEY in Oracle?

A FOREIGN KEY establishes a relationship between two tables.

·        Child value must exist in parent table

·        Maintains referential integrity

Example:

CREATE TABLE departments (

   dept_id NUMBER PRIMARY KEY,

   dept_name VARCHAR2(100)

);

CREATE TABLE employees (

   emp_id   NUMBER PRIMARY KEY,

   name     VARCHAR2(100),

   dept_id  NUMBER,

   CONSTRAINT emp_dept_fk

   FOREIGN KEY (dept_id)

   REFERENCES departments(dept_id)

);

2. What problem does FOREIGN KEY solve?

·        Prevents orphan records

·        Prevents invalid references

·        Maintains parent-child integrity

3. What are parent and child tables?

·        Parent table → Has PRIMARY KEY or UNIQUE key

·        Child table → Has FOREIGN KEY referencing parent

Example: departments = Parent, employees = Child

4. What rules does FOREIGN KEY enforce?

·        Child value must exist in parent

·        Cannot delete parent if child exists (unless cascade)

·        Cannot update parent key if child exists (unless handled)

5. What happens if FOREIGN KEY is violated?

Oracle raises:

·        ORA-02291 → parent key not found

·        ORA-02292 → child record found

Example:

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

If department 50 does not exist → ORA-02291.

6. Does FOREIGN KEY create an index automatically?

No.

·        Indexing FK manually improves DELETE, JOIN, and locking performance:

CREATE INDEX emp_dept_idx ON employees(dept_id);

7. What is ON DELETE CASCADE?

Deletes child rows automatically when parent is deleted:

FOREIGN KEY (dept_id)

REFERENCES departments(dept_id)

ON DELETE CASCADE;

8. What is ON DELETE SET NULL?

Sets child foreign key column to NULL if parent is deleted:

FOREIGN KEY (dept_id)

REFERENCES departments(dept_id)

ON DELETE SET NULL;

9. Can FOREIGN KEY reference UNIQUE key?

Yes — FK can reference PRIMARY KEY or UNIQUE column:

REFERENCES users(email)

10. Can FOREIGN KEY be composite?

Yes — multiple columns must match parent in order and type:

CREATE TABLE order_items (

   order_id NUMBER,

   product_id NUMBER,

   CONSTRAINT oi_fk

   FOREIGN KEY (order_id, product_id)

   REFERENCES orders(order_id, product_id)

);

11. Can FOREIGN KEY be DEFERRABLE?

Yes — validation deferred to COMMIT:

ALTER TABLE employees

ADD CONSTRAINT emp_dept_fk

FOREIGN KEY (dept_id)

REFERENCES departments(dept_id)

DEFERRABLE INITIALLY DEFERRED;

Useful for batch processing.

12. Does FOREIGN KEY allow NULL values?

Yes. NULL means “no relationship”. Use NOT NULL if mandatory.

13. How does FOREIGN KEY affect performance?

Positive: Optimizer understands joins, maintains clean data
Negative: INSERT/UPDATE overhead, DELETE locks if not indexed

Always index foreign key columns.

14. What happens if parent row is deleted without cascade?

Oracle prevents deletion → ORA-02292. Must delete child first or use ON DELETE CASCADE.

15. How to add FOREIGN KEY to existing table?

ALTER TABLE employees

ADD CONSTRAINT emp_dept_fk

FOREIGN KEY (dept_id)

REFERENCES departments(dept_id);

16. How to drop FOREIGN KEY?

ALTER TABLE employees

DROP CONSTRAINT emp_dept_fk;

17. How to check foreign keys in a table?

SELECT constraint_name, table_name

FROM user_constraints

WHERE constraint_type = 'R';

18. Common mistakes

·        Not indexing FK column

·        Ignoring ON DELETE rules

·        Circular FKs without DEFERRABLE

·        Forgetting to handle child rows before deleting parent

19. Real-world example

Bad design:

CREATE TABLE orders (

   order_id NUMBER PRIMARY KEY,

   customer_id NUMBER

);

Better design:

CREATE TABLE orders (

   order_id NUMBER PRIMARY KEY,

   customer_id NUMBER,

   CONSTRAINT orders_cust_fk

   FOREIGN KEY (customer_id)

   REFERENCES customers(customer_id)

);

20. Best Practices

·        Always define FK for relationships

·        Always index FK columns

·        Use ON DELETE CASCADE carefully

·        Use NOT NULL if relationship is mandatory

·        Monitor locking behavior

21. Interview Tip

“A FOREIGN KEY enforces referential integrity by ensuring child values exist in parent table. Prevents orphan records. Unlike primary keys, FK does not automatically create an index, so indexing is recommended for performance.”