🔗 SQL `FOREIGN KEY` – Enforce Relationships Between Tables

In relational databases, data integrity is key. The SQL FOREIGN KEY constraint ensures that relationships between tables remain valid, consistent, and reliable.

This guide will walk you through how FOREIGN KEY works, how to define it, and when to use it.

📘 What is a `FOREIGN KEY` in SQL?

A FOREIGN KEY is a column (or a group of columns) in one table that refers to the primary key in another table. It creates a parent-child relationship between the two tables, enforcing referential integrity.

Why Use a `FOREIGN KEY`?

Prevents inserting invalid data
Maintains relational links
Avoids orphaned records
Supports cascading updates and deletes

🧾 SQL `FOREIGN KEY` Syntax

Add a foreign key during table creation:

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

Add a foreign key to an existing table:

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

✅ Example Use Case

Imagine you have two tables: customers and orders.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100)
);

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

In this case, customer_id in the orders table must match a customer_id in the customers table. You cannot insert an order for a non-existent customer.

🔁 ON DELETE / ON UPDATE Options

Control what happens when referenced data is deleted or updated:

FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Option	Behavior
`CASCADE`	Automatically deletes/updates dependent rows
`SET NULL`	Sets the foreign key column to NULL
`SET DEFAULT`	Sets the foreign key to a default value
`NO ACTION` / `RESTRICT`	Blocks the operation if dependencies exist

⚠️ Things to Keep in Mind

Tip	Reason
Columns must match in data type & size	Ensures data compatibility
`FOREIGN KEY` must reference a `PRIMARY KEY`	Enforces valid one-to-many or many-to-one relationships
Foreign key values must exist in the parent	Prevents invalid or orphaned references
You may need to drop constraints to modify	Use `ALTER TABLE DROP CONSTRAINT` if needed

🧠 Real-World Example

Let’s say you’re building an e-commerce platform. A customer places an order:

The orders table must reference the customers table.
You don’t want an order without a valid customer.
Using a FOREIGN KEY ensures data integrity and helps with clean JOINs in queries.

📝 Summary

FOREIGN KEY links one table to another and enforces relational integrity.
It ensures that the data in the child table matches existing values in the parent.
It’s essential for maintaining clean, relational data in multi-table databases.

Foreign Key Constraint

🔗 SQL FOREIGN KEY – Enforce Relationships Between Tables

📘 What is a FOREIGN KEY in SQL?

Why Use a FOREIGN KEY?

🧾 SQL FOREIGN KEY Syntax