🔗 SQL FOREIGN KEY – Enforce Table Relationships

🧲 Introduction – What is SQL FOREIGN KEY?

A FOREIGN KEY in SQL creates a link between two tables by referencing the PRIMARY KEY or UNIQUE key of another table. It enforces referential integrity, ensuring that the child table values must exist in the parent table.

🎯 In this guide, you’ll learn:

• How to define foreign keys
• The impact on insert, update, and delete operations
• Cascade behaviors and best practices

---

✅ 1. Basic FOREIGN KEY Syntax

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

✅ Links orders.customer_id to customers.id.

---

🔄 2. Add FOREIGN KEY with ALTER TABLE

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

✅ Useful when adding relationships to existing tables.

---

🔁 3. Cascading Options

Option	Behavior
ON DELETE CASCADE	Deletes child rows when parent is deleted
ON UPDATE CASCADE	Updates child rows when parent key is changed
SET NULL	Sets child reference to NULL on delete/update
RESTRICT / NO ACTION	Prevents delete/update if child rows exist

Example:

FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE

---

🧠 4. Behavior and Rules

• Foreign keys must reference a PRIMARY KEY or UNIQUE column
• Referenced values must already exist (or use deferred enforcement)
• Can reference multiple parent tables with compound keys

---

🧱 5. Composite Foreign Key

FOREIGN KEY (dept_id, manager_id)
REFERENCES departments(dept_id, manager_id)

✅ Matches composite primary or unique keys in another table.

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use for true parent-child relationships	Using FKs on lookup tables without need
Cascade deletes only when logical	Accidentally removing related data
Index foreign key columns	Leaving them unindexed (hurts performance)

---

📌 Summary – Recap & Next Steps

The FOREIGN KEY constraint is key for enforcing valid relationships between tables. It allows you to structure data like real-world connections and prevent orphaned records.

🔍 Key Takeaways:

• Links a column to a key in another table
• Enforces referential integrity
• Supports cascades for delete/update actions

⚙️ Real-World Relevance:
Used in customer-order, user-post, product-category, employee-department, and many other relational models.

➡️ Next: Learn about JOINs, CASCADE, or database normalization using foreign keys.

---

❓ FAQ – SQL FOREIGN KEY

❓ What does FOREIGN KEY do in SQL?

✅ It links two tables and ensures data consistency between them.

❓ Can a FOREIGN KEY reference a non-primary column?

✅ Yes, but it must be declared as UNIQUE in the parent table.

❓ What happens if I insert a non-matching foreign key?

❌ SQL throws an error—value must exist in the parent table.

❓ How can I remove a foreign key constraint?

ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;  -- MySQL
ALTER TABLE orders DROP CONSTRAINT fk_customer_id;    -- PostgreSQL / SQL Server

---

Share Now :