Blog › MySQL

March 2026 — 6 min read

MySQL Foreign Key — Syntax, Examples, and Best Practices

Foreign keys are the mechanism that turns a collection of isolated tables into a relational database. They enforce that relationships between rows are always valid, preventing orphaned records and data integrity bugs. This guide covers everything you need to know to use them correctly in MySQL.

What Is a Foreign Key?

A foreign key is a column (or group of columns) in one table that references the primary key of another table. It creates a constraint: MySQL will reject any insert or update that would create a reference to a row that doesn't exist in the parent table.

For example: if you have an orders table with a user_id column, a foreign key constraint ensures that every user_id in orders corresponds to a real row in the users table.

Basic Syntax

You can define a foreign key inline when creating a table, or add it separately with ALTER TABLE.

Inline (at table creation):

CREATE TABLE orders (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Added after table creation:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

ON DELETE and ON UPDATE Options

These clauses control what happens to child rows when the referenced parent row is deleted or its primary key is updated. Choose carefully — the wrong option can cause data loss or leave orphaned rows.

• CASCADE — automatically delete or update child rows to match the parent. Use for tightly coupled data (e.g., order_items when an order is deleted).
• SET NULL — set the foreign key column to NULL when the parent is deleted/updated. The column must allow NULL. Use when the child can exist independently (e.g., a post whose author was deleted).
• RESTRICT — prevent deletion/update of the parent if child rows exist. This is the default if you omit the clause. Use when you want to force explicit cleanup.
• NO ACTION — identical to RESTRICT in MySQL's InnoDB implementation.
• SET DEFAULT — not supported by InnoDB; avoid it.

A Practical Example: E-commerce Schema

CREATE TABLE users (
    id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE orders (
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    total   DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE order_items (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   INT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Here, deleting a user is blocked if they have orders (RESTRICT). Deleting an order automatically removes its line items (CASCADE). Updating a user's id propagates to all their orders (CASCADE).

Common Mistakes

• Mismatched data types. The child column and parent column must have exactly the same type, including sign. An INT UNSIGNED primary key requires an INT UNSIGNED foreign key — plain INT won't work.
• Missing index on the child column. MySQL requires an index on the foreign key column. If you don't create one explicitly, MySQL creates one automatically — but it's good practice to be explicit.
• Using MyISAM instead of InnoDB. Foreign key constraints are only enforced on InnoDB tables. MyISAM silently ignores them.
• Circular dependencies. Be careful when two tables reference each other. Use SET NULL or carefully order inserts/deletes to avoid constraint violations.

Visualise Foreign Keys Before Writing DDL

For anything beyond a few tables, it's much easier to design your relationships visually first and generate the SQL from the diagram. Drawing the lines between tables makes cascade behaviour and cardinality immediately obvious — mistakes that would take hours to debug in raw SQL are visible at a glance.