SQL FOREIGN KEY constraint

SQL Tutorial

Here is a clear, practical, and complete guide to the SQL FOREIGN KEY constraint, with examples, engine differences, and best-practice recommendations.

✅ What a SQL FOREIGN KEY constraint Does

A FOREIGN KEY enforces referential integrity between two tables by ensuring the value in one table must exist in another table.

  • The referencing table contains the foreign key column

  • The referenced table contains the primary (or unique) key

  • Prevents orphaned child rows

  • Supports cascading actions (update/delete)

🔹 Basic Syntax (CREATE TABLE)


✔ Ensures customer_id in orders exists in customers.

🔹 Named FOREIGN KEY (Best Practice)


🔥 Add FOREIGN KEY After Table Creation

PostgreSQL / SQL Server:


MySQL:


🔥 Drop FOREIGN KEY

PostgreSQL / SQL Server:


MySQL:


🔥 CASCADE Options (Very Important)

These control how the DB reacts when the referenced row changes.

▪ ON DELETE CASCADE

Delete child rows when parent is deleted.

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

▪ ON UPDATE CASCADE

Update child rows when parent key is updated.

▪ ON DELETE SET NULL

Set FK column to NULL when parent deleted.

▪ ON DELETE RESTRICT (default)

Prevents deleting parent if child rows exist.

📌 Example With Cascade


✔ Deleting a customer deletes their orders.
✔ Updating customer id updates all associated orders.

🔧 Composite Foreign Keys

Match multiple columns:


Columns must match in number, order, and datatype.

📌 Requirements for a FOREIGN KEY

  1. Referenced column must be:

    • PRIMARY KEY or

    • UNIQUE

  2. Data types must match (or be compatible)

  3. Child rows must match an existing parent row
    (unless the FK column is NULL)

🧠 Behavior Notes

✔ FK columns can be NULL unless NOT NULL is enforced
✔ FK relationships improve data integrity
✔ Cascading deletes must be used carefully in production
✔ Indexes on FK columns improve performance

🧩 Real-World Examples

1. Orders → Customers


2. Comments → Posts


3. Enrollment → Students & Courses


🏎 Best Practices

✔ Always name foreign key constraints
✔ Index FK columns (customer_id, user_id)
✔ Avoid cascading deletes on large tables (can cause big cascades)
✔ Use ON DELETE SET NULL for soft-delete semantics
✔ Normalize schemas so relationships map cleanly to business rules
✔ Validate data before inserts to avoid FK violations

You may also like...