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.

▪ 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...