beginner

Foreign Key Constraints in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-209 min readRuntime Verified

Overview

A FOREIGN KEY constraint establishes a direct referential link between columns in two relational database tables. It enforces referential integrity by guaranteeing that a value in the child table must correspond to an existing, valid value in the referenced parent table (or be NULL). Foreign keys prevent "orphan records" and allow developers to define automated cascading behaviors when parent records are updated or deleted.

What You Will Learn

In this lesson, you will master:
  • Referential Integrity Mechanics: Parent table primary keys vs child table foreign keys.
  • Cascading Rules: Understanding ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT, and ON DELETE NO ACTION.
  • Cascading Updates: Propagating primary key updates safely across relational trees.
  • The Missing Index Trap: Why relational database engines do not automatically index foreign key columns, and why this causes table-locking deadlocks.
  • Self-Referencing Foreign Keys: Modeling hierarchical trees (e.g., employee managers, organizational charts).

Defining Foreign Keys

1. Basic Parent-Child Relationship

Consider a standard e-commerce relationship between customers (parent) and orders (child):

sql
-- Parent Table
CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

-- Child Table with Explicit Foreign Key Constraint
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_total NUMERIC(10, 2) NOT NULL,
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Referential Actions on Parent Deletion (ON DELETE)

When a parent row in customers is deleted, how should the database respond to existing child rows in orders? SQL provides four primary policies:

| Action Policy | Behavior when Parent Row is Deleted | Common Use Case | | :--- | :--- | :--- | | RESTRICT / NO ACTION | Rejects the delete operation and throws an error if any child rows exist | Financial transactions, orders, invoices (prevent accidental loss of audit history) | | CASCADE | Automatically deletes all matching child rows in the child table | Child details strictly dependent on parent (e.g. order_items belonging to an order) | | SET NULL | Sets the child column value to NULL (Child column must permit NULLs) | Optional relationships (e.g., assigning a ticket to an agent; if agent leaves, ticket becomes unassigned) | | SET DEFAULT | Resets the child column value to its specified column default | Fallback or system default accounts |

sql
-- Example: Order Items should automatically delete if their parent order is cancelled/deleted
CREATE TABLE order_items (
    item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price_each NUMERIC(10, 2) NOT NULL,
    
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) 
        REFERENCES orders(order_id)
        ON DELETE CASCADE
);

The Critical "Missing Index" Performance Trap

Critical Database Architecture Warning

When you define a PRIMARY KEY, all database engines automatically create a backing B-Tree index. Database engines do NOT automatically create an index on FOREIGN KEY columns in the child table!

Why is an index on child foreign key columns essential?

  1. Join Acceleration: Almost every query joining parent to child (FROM customers c JOIN orders o ON c.customer_id = o.customer_id) must search the child table's foreign key column. Without an index, this forces a full table scan.
  2. Preventing Table Locks on Parent Deletes: If you delete a customer from customers, the database must verify if any orders exist for that customer. Without an index on orders.customer_id, PostgreSQL and MySQL may acquire an exclusive table-level lock on the entire orders table while scanning it, freezing all concurrent application inserts!
sql
-- ALWAYS create an index on your foreign key columns!
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Self-Referencing Foreign Keys (Hierarchical Trees)

A table can reference itself to model recursive hierarchies, such as employee management chains or nested category trees:

sql
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    manager_id INT,  -- Self-reference pointer
    
    CONSTRAINT fk_employees_manager
        FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

Common Mistakes & Gotchas

  • Mismatched Data Types: The foreign key column and the referenced primary key column must have identical data types and signedness. Referencing a BIGINT with an INT or an INT UNSIGNED with a signed INT fails with type mismatch errors.
  • Circular Foreign Key Deadlocks: Defining Table A with a foreign key to Table B, and Table B with a foreign key to Table A, prevents row insertion into either table unless one constraint is marked DEFERRABLE INITIALLY DEFERRED.

Interview Perspective

SQL Interview Question: Why should you virtually always create an index on foreign key columns in child tables?

Key Talking Points for Candidates:

  1. Join Performance: Most relational queries join on parent.id = child.parent_id. An index avoids full table scans on the child table.
  2. Locking & Concurrency: Deleting or updating a parent record requires the database to check the child table to enforce RESTRICT or execute CASCADE. Without an index on the child foreign key, the engine must scan the entire child table, taking heavy locks that block concurrent writes and cause deadlocks.

Interactive Practice Challenge

Challenge: Project Task Assignment Constraint

Create a tasks table with columns task_id (primary key), title, and assignee_id. Connect assignee_id to an existing users(user_id) table. If a user is deleted from the system, their assigned tasks should not be deleted; instead, assignee_id should automatically be set to NULL.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.