Foreign Keys in SQL

🔗 Understanding Foreign Keys in SQL: A Complete Guide (Oracle-Focused)

✅ What is a Foreign Key?

A foreign key is a constraint used to establish a relationship between two tables in a relational database. It ensures referential integrity by enforcing that the value in one table's column must match a value in another table’s primary key or unique column.

In simpler terms:

A foreign key in a child table points to a primary key in a parent table.

---

🎯 Purpose of a Foreign Key

Foreign keys serve several essential purposes:

✅ Ensure Data Integrity – Prevent orphan records (e.g., an order with a customer that doesn't exist).

🔗 Enforce Relationships – Define how data in one table depends on data in another.

🚫 Restrict or Cascade Deletes/Updates – Control what happens in the child table if the parent data changes.

---

🛠 Real-World Example

Imagine two tables:

CUSTOMERS (customer_id PRIMARY KEY, name, email)
ORDERS (order_id PRIMARY KEY, customer_id FOREIGN KEY, order_date)

Here:

Each order must belong to a valid customer.

ORDERS.customer_id is a foreign key referencing CUSTOMERS.customer_id.

---

🧾 Foreign Key Syntax (Oracle SQL)

🔹 1. During Table Creation (Inline or Out-of-Line)

CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_date DATE,
CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

---

🔹 2. Add to Existing Table

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

---

⚙️ Optional Clauses

You can control behavior on deletion with:

ON DELETE CASCADE – Automatically deletes child rows when parent is deleted.

ON DELETE SET NULL – Sets the child foreign key to NULL when parent is deleted.

✅ Example with CASCADE:

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE;

---

🔐 How Foreign Keys Relate to Primary Keys

A foreign key must reference a column that is either a PRIMARY KEY or has a UNIQUE constraint.

You cannot insert a child row with a foreign key value unless the corresponding parent value exists.

This maintains a hierarchical integrity between parent and child data.

---

🧠 Summary

Constraint Type Purpose Defined On References

Primary Key Uniquely identifies a record Parent table —
Foreign Key Links to a record in another table Child table Parent’s PK or unique column

---

📘 Final Thoughts

Foreign keys are not just about enforcing rules — they reflect the business relationships between entities in your data model. Using them effectively ensures clean, reliable, and well-connected data.

So the next time you’re modeling tables in Oracle or any SQL-based system, make sure to define foreign keys thoughtfully. They’re the glue holding your relational model together.