Foreign Keys — MySQL

MySQL Foreign Keys

A foreign key (FK) is a column (or group of columns) in one table whose values must match existing values in a column of another table. Foreign keys enforce referential integrity — the guarantee that relationships between rows are always valid. Without them, you can end up with orphaned orders pointing to deleted customers, or invoice items referencing products that no longer exist.

Basic Foreign Key Syntax

SQL

CREATE TABLE categories (
  id   INT          NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE products (
  id          INT            NOT NULL AUTO_INCREMENT,
  name        VARCHAR(255)   NOT NULL,
  price       DECIMAL(10, 2) NOT NULL,
  category_id INT,
  PRIMARY KEY (id),
  CONSTRAINT fk_product_category
    FOREIGN KEY (category_id)
    REFERENCES categories (id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

Note

Always name your constraints (e.g., fk_product_category). Named constraints produce clearer error messages and are much easier to drop or inspect later.

ON DELETE Actions — All 5 Options

The ON DELETE clause controls what happens to child rows when a referenced parent row is deleted.

Action	Exact Behavior	InnoDB Timing	Best For
RESTRICT	Reject the DELETE if any child rows reference the parent row.	Checked immediately during the statement	Strict data safety — the default
NO ACTION	Same rejection behavior as RESTRICT in MySQL InnoDB.	Checked at end of statement (deferred in standard SQL)	Standard SQL equivalent — behaves like RESTRICT in MySQL
CASCADE	Automatically delete all child rows that reference the deleted parent.	Immediate	Log entries, order items, or any record that cannot exist without its parent
SET NULL	Set the FK column to NULL in all matching child rows. The FK column must be nullable.	Immediate	Optional relationships where the child can exist without a parent
SET DEFAULT	Set the FK column to its default value. Not reliably supported in InnoDB.	Immediate	Avoid in InnoDB — use SET NULL instead

Note

RESTRICT and NO ACTION behave identically in MySQL InnoDB because InnoDB does not support truly deferred constraint checking. The difference matters in databases like PostgreSQL where DEFERRABLE INITIALLY DEFERRED constraints exist.

SQL

-- CASCADE: deleting a post automatically deletes all its comments
CREATE TABLE comments (
  id      INT  NOT NULL AUTO_INCREMENT,
  post_id INT  NOT NULL,
  body    TEXT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_comment_post
    FOREIGN KEY (post_id) REFERENCES posts (id)
    ON DELETE CASCADE
);

-- SET NULL: deleting a category nullifies the product's category_id
CREATE TABLE products (
  id          INT            NOT NULL AUTO_INCREMENT,
  category_id INT            DEFAULT NULL,    -- must be nullable for SET NULL
  PRIMARY KEY (id),
  CONSTRAINT fk_product_cat
    FOREIGN KEY (category_id) REFERENCES categories (id)
    ON DELETE SET NULL
);

-- RESTRICT: prevent deleting a user who still has orders
CREATE TABLE orders (
  id      INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_order_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE RESTRICT
);

ON UPDATE Actions

ON UPDATE uses the same 5 options as ON DELETE, but applies when the referenced parent primary key value is changed.

SQL

-- CASCADE update: changing the parent PK propagates to all child FK columns
CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  CONSTRAINT fk_item_order
    FOREIGN KEY (order_id) REFERENCES orders (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Tip

With AUTO_INCREMENT surrogate primary keys, ON UPDATE CASCADE is rarely needed because PKs should never change. It is more relevant when using natural keys (e.g., a product code that gets corrected).

Multi-Column Foreign Key

A foreign key can span multiple columns when the referenced parent table uses a composite primary key:

SQL

-- Parent with composite PK
CREATE TABLE warehouse_locations (
  warehouse_id  INT         NOT NULL,
  aisle         VARCHAR(10) NOT NULL,
  shelf         INT         NOT NULL,
  capacity      INT,
  PRIMARY KEY (warehouse_id, aisle, shelf)
);

-- Child with matching composite FK
CREATE TABLE inventory (
  product_id   INT NOT NULL,
  warehouse_id INT NOT NULL,
  aisle        VARCHAR(10) NOT NULL,
  shelf        INT NOT NULL,
  quantity     INT NOT NULL,
  PRIMARY KEY (product_id, warehouse_id, aisle, shelf),
  CONSTRAINT fk_inventory_location
    FOREIGN KEY (warehouse_id, aisle, shelf)
    REFERENCES warehouse_locations (warehouse_id, aisle, shelf)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

Self-Referencing Foreign Key

A table can have a FK that references its own PK. This models hierarchical data such as organizational charts, category trees, and threaded comments.

SQL

CREATE TABLE categories (
  id        INT          NOT NULL AUTO_INCREMENT,
  name      VARCHAR(100) NOT NULL,
  parent_id INT          DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_category_parent
    FOREIGN KEY (parent_id)
    REFERENCES categories (id)
    ON DELETE SET NULL
);

-- Insert top-level category (no parent)
INSERT INTO categories (name, parent_id) VALUES ('Electronics', NULL);

-- Insert sub-categories
INSERT INTO categories (name, parent_id) VALUES ('Phones', 1);
INSERT INTO categories (name, parent_id) VALUES ('Smartphones', 2);
INSERT INTO categories (name, parent_id) VALUES ('Budget Phones', 2);

-- Query the hierarchy with a self-join
SELECT
  child.name     AS category,
  parent.name    AS parent_category
FROM categories child
LEFT JOIN categories parent ON child.parent_id = parent.id;

Adding a Foreign Key to an Existing Table

SQL

-- The FK column must already exist; all existing values must satisfy the constraint
ALTER TABLE products
  ADD CONSTRAINT fk_product_category
  FOREIGN KEY (category_id)
  REFERENCES categories (id)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

Warning

Before adding a FK to an existing table, ensure all values in the child column already exist in the parent table. MySQL will reject the ALTER TABLE if any orphaned values are found. Check first with a query like

SELECT COUNT(*) FROM products p WHERE p.category_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM categories c WHERE c.id = p.category_id);

Named Constraints and Dropping a Foreign Key

SQL

-- Find all FK constraint names if you don't know them
SELECT
  kcu.TABLE_NAME,
  kcu.COLUMN_NAME,
  kcu.CONSTRAINT_NAME,
  kcu.REFERENCED_TABLE_NAME,
  kcu.REFERENCED_COLUMN_NAME,
  rc.UPDATE_RULE,
  rc.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE kcu
JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
  ON kcu.CONSTRAINT_NAME  = rc.CONSTRAINT_NAME
  AND kcu.TABLE_SCHEMA    = rc.CONSTRAINT_SCHEMA
WHERE kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.TABLE_NAME;

-- Drop a foreign key by constraint name
ALTER TABLE products DROP FOREIGN KEY fk_product_category;

-- Drop the associated index if no longer needed
ALTER TABLE products DROP INDEX fk_product_category;

Disabling FK Checks for Bulk Loads

During bulk data imports or when reloading a dump, you may need to insert data in a different order than the FK constraints require (e.g., child rows before parent rows). Temporarily disable FK checks:

SQL

-- Disable FK checks for this session only
SET foreign_key_checks = 0;

-- Now you can insert in any order
LOAD DATA INFILE '/tmp/products.csv'  INTO TABLE products  ...;
LOAD DATA INFILE '/tmp/categories.csv' INTO TABLE categories ...;

-- Re-enable FK checks
SET foreign_key_checks = 1;

-- Verify no orphaned rows were introduced
SELECT COUNT(*) AS orphans
FROM products p
WHERE p.category_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM categories c WHERE c.id = p.category_id);

Warning

Always re-enable foreign_key_checks after your import. Leaving it OFF allows orphaned records to silently enter the database. Also verify integrity after re-enabling — fix any violations before they cause application errors.

FK Locking Behavior

When MySQL evaluates a FK constraint, it acquires a shared lock on the referenced parent row. This prevents the parent row from being deleted while the child insert/update is in progress. Implications:

Heavy child inserts can briefly lock parent rows, increasing lock wait times on the parent table.
The child table FK column must be indexed — without an index, every parent DELETE/UPDATE causes a full scan of the child table to check for referencing rows.

SQL

-- MySQL automatically creates an index on the FK column IF one does not exist
-- Verify the index was created:
SHOW INDEXES FROM order_items;
-- You should see an index on order_id

-- The FK index is a normal B-tree index and can be used by application queries
EXPLAIN SELECT * FROM order_items WHERE order_id = 42;

-- If the FK column has no index, every parent DELETE causes a full child scan:
-- Add the index manually if needed
ALTER TABLE order_items ADD INDEX idx_fk_order_id (order_id);

Note

MySQL creates an index on the FK column automatically only when creating the FK — it does not create one retroactively if you add a FK to a column that already has data but no index. Always verify with SHOW INDEX.

FK and Online Schema Changes

Adding or dropping a FK on a large table in production requires care. The INPLACE algorithm is available for some FK operations in MySQL 8.0, but not all:

SQL

-- MySQL 8.0: check if INPLACE is possible
ALTER TABLE order_items
  ADD CONSTRAINT fk_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id),
  ALGORITHM=INPLACE, LOCK=NONE;   -- fails if not supported; falls back to COPY

-- For large tables in production, use pt-online-schema-change from Percona Toolkit
-- to add/drop FKs without locking the table:
-- pt-online-schema-change --alter "ADD CONSTRAINT fk_items_order
--   FOREIGN KEY (order_id) REFERENCES orders(id)"
-- D=mydb,t=order_items --execute

Foreign Key Requirements Checklist

Requirement	Why
Both tables must use InnoDB	MyISAM silently parses FKs but never enforces them
Parent column must be PRIMARY KEY or UNIQUE	FK must reference a uniquely identified row
Child and parent column data types must match exactly	Including sign for integers (INT vs INT UNSIGNED)
String columns must share the same character set and collation	Mismatched charsets prevent the FK from being created
FK column in child table should be indexed	MySQL creates one automatically, but verify it exists

SQL

-- Common mistake: signed vs unsigned mismatch
CREATE TABLE parent (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);

-- This will fail — child.parent_id is signed, parent.id is unsigned
CREATE TABLE child (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  parent_id INT,   -- signed — WRONG
  FOREIGN KEY (parent_id) REFERENCES parent(id)
);

-- Correct: match the sign
CREATE TABLE child (
  id        INT          AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED,   -- unsigned — matches parent
  FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Checking for FK Violations Before Adding a Constraint

SQL

-- Before adding a FK, find orphaned child rows that would violate it
SELECT p.id AS orphaned_product_id, p.category_id
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.category_id IS NOT NULL
  AND c.id IS NULL;

-- Fix orphaned rows before adding the FK:
-- Option A: delete orphans
DELETE p FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.category_id IS NOT NULL AND c.id IS NULL;

-- Option B: set to NULL (if the column is nullable)
UPDATE products p
LEFT JOIN categories c ON p.category_id = c.id
SET p.category_id = NULL
WHERE p.category_id IS NOT NULL AND c.id IS NULL;

-- Now you can safely add the FK
ALTER TABLE products
  ADD CONSTRAINT fk_product_category
  FOREIGN KEY (category_id) REFERENCES categories(id)
  ON DELETE SET NULL;

Cascading DELETE Example — End to End

SQL

-- Schema: blog posts with cascading deletes
CREATE TABLE posts (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  title   VARCHAR(255) NOT NULL,
  user_id INT NOT NULL,
  CONSTRAINT fk_post_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
);

CREATE TABLE comments (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  body    TEXT NOT NULL,
  CONSTRAINT fk_comment_post
    FOREIGN KEY (post_id) REFERENCES posts(id)
    ON DELETE CASCADE
);

CREATE TABLE comment_likes (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  comment_id INT NOT NULL,
  user_id    INT NOT NULL,
  CONSTRAINT fk_like_comment
    FOREIGN KEY (comment_id) REFERENCES comments(id)
    ON DELETE CASCADE
);

-- Deleting a user cascades 3 levels deep:
-- DELETE user -> deletes posts -> deletes comments -> deletes comment_likes
DELETE FROM users WHERE id = 42;
-- All posts, comments, and comment_likes for user 42 are automatically removed

Viewing All Foreign Keys in a Database

SQL

-- Complete FK dependency map for the current database
SELECT
  kcu.TABLE_NAME           AS child_table,
  kcu.COLUMN_NAME          AS child_column,
  kcu.CONSTRAINT_NAME      AS constraint_name,
  kcu.REFERENCED_TABLE_NAME AS parent_table,
  kcu.REFERENCED_COLUMN_NAME AS parent_column,
  rc.UPDATE_RULE,
  rc.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE kcu
JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
  ON kcu.CONSTRAINT_NAME  = rc.CONSTRAINT_NAME
  AND kcu.TABLE_SCHEMA    = rc.CONSTRAINT_SCHEMA
WHERE kcu.TABLE_SCHEMA = DATABASE()
  AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY kcu.TABLE_NAME, kcu.CONSTRAINT_NAME;

Deferrable FK Constraints — MySQL Limitation

Standard SQL supports DEFERRABLE constraints — constraints that are checked at the end of a transaction rather than immediately. MySQL InnoDB does NOT support deferred constraints. This matters for circular FK dependencies:

SQL

-- Circular FK problem: table A references B, and B references A
-- Standard SQL solution (NOT supported by MySQL):
-- ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b(id) DEFERRABLE INITIALLY DEFERRED;
-- ALTER TABLE b ADD FOREIGN KEY (a_id) REFERENCES a(id) DEFERRABLE INITIALLY DEFERRED;

-- MySQL workaround: use SET foreign_key_checks = 0 to insert both rows, then re-enable
SET foreign_key_checks = 0;

INSERT INTO table_a (id, b_id) VALUES (1, 10);   -- b_id = 10 does not exist yet
INSERT INTO table_b (id, a_id) VALUES (10, 1);   -- creates the circular reference

SET foreign_key_checks = 1;
-- Now both rows exist and the circular FK is satisfied

-- Best practice: avoid circular FK dependencies in schema design
-- Use a nullable FK in one direction to break the cycle

FK Error Messages Decoded

Error	Cause	Fix
ERROR 1452: Cannot add or update a child row: a foreign key constraint fails	INSERT or UPDATE references a parent value that does not exist	Insert the parent row first, or check for typos in the FK value
ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails	DELETE or UPDATE on a parent row that has child rows pointing to it (RESTRICT behavior)	Delete or update child rows first, or use CASCADE/SET NULL on the FK
ERROR 1005: Cannot create table (errno: 150)	FK definition is invalid: type mismatch, missing parent index, or wrong engine	Verify data types match, parent column has an index, and both tables use InnoDB
ERROR 3730: Cannot drop table with foreign key constraint	Trying to DROP a parent table while child tables still reference it	Drop child table first, or use SET foreign_key_checks=0 (with caution)

FK Best Practices Summary

Always name FK constraints explicitly — anonymous constraints are harder to drop and produce confusing error messages.
Use CASCADE for records that cannot exist without their parent (order_items without an order, comments without a post).
Use SET NULL for optional relationships where the child can still be useful without a parent (products without a category).
Use RESTRICT (the default) when orphaned child rows would represent a data integrity error.
Always index the FK column in the child table — without an index, parent DELETE/UPDATE operations scan the entire child table.
Test FK behavior in development by running the referenced DELETE/UPDATE operations and verifying child table behavior.
When disabling FK checks for a bulk load, always verify integrity immediately after re-enabling checks.