MySQL Foreign Key
A foreign key links a column in one table to the primary key of another, letting MySQL enforce that related rows always point to something that actually exists.
Referencing Another Table
A FOREIGN KEY constraint names a column in the current (child) table and ties it to a column -- almost always a primary key or unique key -- in another (parent) table. MySQL then refuses any insert or update that would leave the child column pointing at a value that does not exist in the parent table, a rule called referential integrity.
Link orders to customers
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Storage Engine Requirement
Foreign keys are only enforced by the InnoDB storage engine; MyISAM tables accept the syntax but silently ignore the constraint. Since InnoDB has been the default engine since MySQL 5.5, most new tables support foreign keys automatically, but it is worth confirming with SHOW TABLE STATUS if you inherit an older schema.
ON DELETE and ON UPDATE Actions
A foreign key can specify what happens to child rows when the referenced parent row is deleted or its key is updated. Without an explicit action, MySQL defaults to RESTRICT, which blocks the parent change entirely while matching child rows exist.
Cascade deletes to child rows
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);- RESTRICT (default): blocks the parent delete/update if matching child rows exist.
- CASCADE: automatically deletes or updates matching child rows along with the parent.
- SET NULL: sets the child's foreign key column to NULL (requires the column to allow NULL).
- NO ACTION: behaves like RESTRICT in MySQL's implementation.
