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.