FOREIGN KEY
Declare that one table's column must reference a real row in another, and let InnoDB refuse the writes that would break it.
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name, ...) [ON DELETE reference_option] [ON UPDATE reference_option]A FOREIGN KEY states that the values in one or more columns must exist as a key in another table. It turns "every order belongs to a customer" from a convention your application tries to honour into a rule the server will not let you break.
How InnoDB enforces it
InnoDB checks constraints immediately, statement by statement — the check is not deferred to commit. An INSERT or UPDATE pointing at a parent row that does not exist fails with error 1452; deleting a parent that still has children fails with 1451 unless you declared another action with ON DELETE. The permitted actions are NO ACTION, RESTRICT, CASCADE and SET NULL. NO ACTION is the action MySQL applies when the clause is omitted, and for InnoDB it is equivalent to RESTRICT; SET DEFAULT parses but InnoDB rejects any table definition that uses it.
Requirements
Both sides must be indexed with the key columns first and in the same order. InnoDB creates the index on the referencing table automatically if one does not exist; the referenced table must already have its index. Types must correspond: for INTEGER and DECIMAL the size and sign must match exactly, while string lengths may differ but character set and collation must be the same.
MyISAM ignores them
"For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications." A MyISAM table accepts the clause, reports no error, and enforces nothing — which is how a schema can look constrained for years while accumulating orphans.
Example
CREATE TABLE customers (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-- Must match customers.id exactly: BIGINT UNSIGNED, not BIGINT
customer_id BIGINT UNSIGNED NOT NULL,
placed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- Naming the constraint makes the error message and the later
-- ALTER TABLE ... DROP FOREIGN KEY readable
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id)
ON DELETE RESTRICT -- refuse to delete a customer with orders
ON UPDATE CASCADE -- follow the parent id if it ever changes
) ENGINE = InnoDB;
INSERT INTO orders (customer_id) VALUES (999);
-- ERROR 1452 (23000): Cannot add or update a child row:
-- a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer` ...)When to use it
- Stop an application bug from writing order rows that point at a deleted customer.
- Model a reporting hierarchy where a row references another row in the same table.
- Load a large dump quickly, then verify referential integrity before opening the database to traffic.
More examples
A self-referencing foreign key
The standard shape for org charts, category trees and threaded comments. ON DELETE SET NULL requires the column to be nullable, which is also what lets the root row exist; note that self-referential ON UPDATE CASCADE behaves like RESTRICT, so plan on the parent key never changing.
CREATE TABLE employees (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
-- NULL means "no manager" - the root of the tree
manager_id INT UNSIGNED NULL,
CONSTRAINT fk_employees_manager
FOREIGN KEY (manager_id) REFERENCES employees (id)
ON DELETE SET NULL -- promote reports to the root rather than delete them
) ENGINE = InnoDB;
-- Insert the root first: the parent row must exist before the child
INSERT INTO employees (id, name, manager_id) VALUES (1, 'Rosa', NULL);
INSERT INTO employees (id, name, manager_id) VALUES (2, 'Ivan', 1);A composite foreign key and the index it needs
Multi-column keys are where ordering rules bite: the referenced columns must be the leading columns of an index in the same order, so FOREIGN KEY (user_id, tenant_id) against this primary key would be rejected. Reach for a composite FK in multi-tenant schemas where the tenant id belongs in every key.
CREATE TABLE tenant_users (
tenant_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
role VARCHAR(30) NOT NULL,
PRIMARY KEY (tenant_id, user_id) -- this is the referenced key
) ENGINE = InnoDB;
CREATE TABLE audit_entries (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
action VARCHAR(60) NOT NULL,
-- Columns must be listed in the same order as the referenced key
CONSTRAINT fk_audit_tenant_user
FOREIGN KEY (tenant_id, user_id) REFERENCES tenant_users (tenant_id, user_id)
ON DELETE CASCADE
) ENGINE = InnoDB;Bulk loading, then verifying what you skipped
Disabling the checks is the normal way to restore a dump whose tables arrive in the wrong order, or to break a circular dependency. The point of the follow-up query is that re-enabling the variable proves nothing — the anti-join is what tells you whether the restore was clean.
SET foreign_key_checks = 0; -- session-scoped; lets tables load in any order
SOURCE /backups/shop_dump.sql;
SET foreign_key_checks = 1; -- does NOT re-validate anything loaded above
-- So check by hand before trusting the data
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL; -- orphaned orders, if any
-- Or make InnoDB verify the rows by re-adding the constraint while
-- foreign_key_checks is enabled: that path uses ALGORITHM=COPY and
-- fails with error 1452 if any child row has no parent. A plain
-- rebuild (ALTER TABLE ... FORCE) does not re-check existing rows.
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id);
Discussion