Foreign Key Constraints in SQL
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, andON 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):
-- 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 |
-- 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?
- 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. - Preventing Table Locks on Parent Deletes: If you delete a customer from
customers, the database must verify if anyordersexist for that customer. Without an index onorders.customer_id, PostgreSQL and MySQL may acquire an exclusive table-level lock on the entireorderstable while scanning it, freezing all concurrent application inserts!
-- 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:
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
BIGINTwith anINTor anINT UNSIGNEDwith a signedINTfails 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:
- Join Performance: Most relational queries join on
parent.id = child.parent_id. An index avoids full table scans on the child table. - Locking & Concurrency: Deleting or updating a parent record requires the database to check the child table to enforce
RESTRICTor executeCASCADE. 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
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.