Foreign Keys & Referential Integrity
IntermediateInnoDB foreign keys enforce parent-child integrity with ON DELETE/UPDATE CASCADE, SET NULL, or RESTRICT; always index the FK column to avoid full-table scans on parent updates.
Overview
Foreign keys in InnoDB enforce referential integrity at the database level: you cannot insert a child row that references a non-existent parent, and you cannot delete a parent that has children (without CASCADE or SET NULL). This is the strongest guarantee — even if application code has bugs, the database rejects invalid operations. InnoDB automatically acquires shared locks on the parent row when validating the foreign key on insert, which can cause contention. The ON DELETE and ON UPDATE clauses control cascading behaviour: CASCADE propagates changes automatically, SET NULL nullifies the FK column, and RESTRICT (default) rejects the operation. Always index the FK column — MySQL performs a full table scan on the child table when updating or deleting from the parent if no index exists.
Defining foreign keys with cascade rules
Foreign keys are defined in CREATE TABLE or added with ALTER TABLE. Choose cascade behavior based on your domain model.
-- Parent table
CREATE TABLE customers (
id BIGINT NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL UNIQUE,
PRIMARY KEY (id)
);
-- Child table with FK and appropriate cascade
CREATE TABLE orders (
id BIGINT NOT NULL AUTO_INCREMENT,
customer_id BIGINT NOT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'PENDING',
total DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id),
INDEX idx_customer_id (customer_id), -- ALWAYS index the FK column
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (id)
ON DELETE RESTRICT -- prevent deleting a customer with orders
ON UPDATE CASCADE -- if customer PK changes, update FK automatically
);
-- Cascade options:
-- CASCADE: propagate DELETE/UPDATE to child rows automatically
-- SET NULL: nullify FK column on parent DELETE/UPDATE (column must be nullable)
-- RESTRICT: reject the parent operation if children exist (default)
-- NO ACTION: same as RESTRICT but deferred check (InnoDB treats same as RESTRICT)
-- SET DEFAULT: not supported by InnoDB
-- Add FK to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT;Performance: FK index requirement and deadlocks
MySQL's FK enforcement requires an index on the child column. Without it, every parent UPDATE/DELETE triggers a full table scan on the child. FKs can also cause unexpected deadlocks.
-- DIAGNOSE: check for missing FK indexes
SELECT
fk.TABLE_NAME AS child_table,
fk.COLUMN_NAME AS fk_column,
fk.REFERENCED_TABLE_NAME AS parent_table,
IF(idx.INDEX_NAME IS NULL, 'MISSING INDEX!', idx.INDEX_NAME) AS index_status
FROM information_schema.KEY_COLUMN_USAGE fk
LEFT JOIN information_schema.STATISTICS idx
ON idx.TABLE_SCHEMA = fk.TABLE_SCHEMA
AND idx.TABLE_NAME = fk.TABLE_NAME
AND idx.COLUMN_NAME = fk.COLUMN_NAME
WHERE fk.REFERENCED_TABLE_NAME IS NOT NULL
AND fk.TABLE_SCHEMA = 'mydb';
-- FK deadlock scenario:
-- Thread 1: INSERT INTO orders (customer_id=5) → S-lock on customers row 5
-- Thread 2: INSERT INTO orders (customer_id=5) → S-lock on customers row 5
-- Thread 1: UPDATE customers SET email=... WHERE id=5 → needs X-lock → blocked by T2's S-lock
-- Thread 2: same → DEADLOCK
-- Prevention: always UPDATE parent and INSERT child in the same transaction order
-- Or use SELECT ... FOR UPDATE on parent before inserting childrenDisabling FK checks for bulk operations
Loading large datasets is faster with FK checks disabled. Always re-enable and verify integrity afterwards.
-- Bulk load without FK overhead (e.g. data migration, seeding)
SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, customer_id, status, total);
SET FOREIGN_KEY_CHECKS = 1;
-- Verify referential integrity after bulk load
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
-- Returns orphaned orders — fix before re-enabling in production
-- JPA: FK in Spring Boot entity mapping
@Entity
public class Order {
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "customer_id",
nullable = false,
foreignKey = @ForeignKey(name = "fk_orders_customer"))
private Customer customer;
}Key Points to Remember
- 1InnoDB is the only MySQL engine that enforces FK constraints — MyISAM silently accepts invalid data.
- 2Always add an index on the FK column — without it MySQL scans the entire child table on every parent UPDATE/DELETE.
- 3RESTRICT is the safe default; CASCADE is convenient but dangerous on large tables (one delete triggers thousands of child deletes).
- 4FK validation acquires a shared lock on the parent row — under high insert concurrency this can cause contention and deadlocks.
- 5Use SET FOREIGN_KEY_CHECKS=0 only for bulk loads; verify integrity with a LEFT JOIN check before re-enabling.
- 6FK constraints catch bugs in application code but add write overhead — some large-scale systems enforce integrity at the application layer instead.
Interview Questions
Sign in to ask AriaWhat is the difference between ON DELETE CASCADE and ON DELETE RESTRICT?
Why must you index the FK column and what happens if you forget?
Explain how FK constraints can cause deadlocks in a high-concurrency insert workload.
Is it safe to use ON DELETE CASCADE on a customers → orders relationship in a production system? Why?
How would you safely migrate data into a table with FK constraints in a production database?
Ask Aria about Foreign Keys & Referential Integrity
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.