MySQL Foreign Key — Syntax, Examples, and Best Practices

What Is a MySQL Foreign Key?

A MySQL foreign key is a column constraint that references the primary key or a unique index of another table. MySQL rejects any INSERT or UPDATE that would create a reference to a non-existent parent row. According to the Stack Overflow Developer Survey 2025, 40.5% of all developers work with MySQL, making foreign key constraints one of the most widely-used referential integrity tools in production databases.

Take a simple example. You have an orders table with a user_id column. A foreign key on that column ensures every user_id maps to a real row in the users table. No orphan references, no silent data corruption. The constraint runs at the database level, independent of any application code.

One critical detail: the MySQL 8.4 Reference Manual confirms that InnoDB and NDB are the only storage engines that actually enforce foreign key checks. MyISAM accepts the syntax without complaint, then ignores it entirely. If your schema depends on referential integrity, confirm your tables use InnoDB before defining a single constraint.

Basic Syntax

MySQL foreign key syntax follows two patterns: inline during CREATE TABLE, or added later with ALTER TABLE. Both work identically at runtime. The inline approach is simpler for new schemas; ALTER TABLE is what you'll use when adding constraints to existing tables.

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;

Name your constraints explicitly. The fk_orders_user pattern keeps error messages readable and lets you drop or modify the constraint by name later. MySQL generates names automatically when you omit the constraint name, but the generated names are cryptic and hard to work with during debugging.

After adding a constraint, verify it with SHOW CREATE TABLE orders\G. You'll see the constraint listed with its name, referenced columns, and configured actions. Do this as a habit, especially after ALTER TABLE operations.

ON DELETE and ON UPDATE Options

These clauses control what MySQL does to child rows when a parent row is deleted or its primary key changes. The wrong choice here causes silent data loss or blocks operations you didn't expect to block. What's the right choice? It depends on whether child rows should survive independently of the parent.

If you omit the clause entirely, MySQL defaults to RESTRICT. That's a safe default for most cases, but it's better to be explicit so the intent is clear to anyone reading the schema later.

A Practical Example: E-commerce Schema

Here's a three-table e-commerce schema where each foreign key action reflects a deliberate data lifecycle decision:

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
);

Deleting a user is blocked if they have orders. That history matters for finance. Deleting an order automatically removes its line items, since an order_item without an order has no meaning. Updating a user's primary key (rare, but possible) propagates to all their orders via CASCADE.

Designing these relationships visually first makes the cascade choices immediately obvious. See the crow's foot notation guide for how to read and draw cardinality lines before writing any DDL. The database schema examples post also covers real-world multi-table layouts built on the same principles.

Error 1215: Cannot Add Foreign Key Constraint

Error 1215 is the most common foreign key failure. It fires whenever MySQL can't validate the constraint you're adding. The root cause is almost always one of three things.

Type mismatch

The child column type must match the parent exactly, including UNSIGNED. A plain INT child referencing an INT UNSIGNED parent fails every time. Same with INT vs BIGINT, or different character sets on string columns. Check your column definitions side by side before adding the constraint.

Missing index on the referenced column

The parent column must be indexed. It's usually the primary key, but if you're referencing a non-PK column, you need an explicit UNIQUE index on it. MySQL won't reference a column it can't guarantee is unique — otherwise one foreign key value could map to multiple parent rows, which makes referential integrity undefined.

Engine mismatch

Both tables must use InnoDB. MyISAM on either side causes immediate failure. To check the engine on an existing table, run SHOW TABLE STATUS WHERE Name = 'your_table'\G and look at the Engine field.

To diagnose any 1215 error quickly, run:

SHOW ENGINE INNODB STATUS\G

Scroll to the LATEST FOREIGN KEY ERROR section. It tells you exactly which column or type caused the rejection, which is much faster than guessing. The MySQL 8.4 Reference Manual documents the full list of constraint validation rules if you need to go deeper.

Performance Considerations

Adding foreign keys introduces a modest performance cost. Every INSERT, UPDATE, and DELETE on a child table triggers an index lookup against the parent to verify the reference. That lookup hits a B-tree index, so it's fast in practice, but it isn't free.

The bigger concern is bulk data loads. MySQL checks every row individually during large imports, which can extend load times significantly. The standard fix is to disable checks for the duration of the load:

SET FOREIGN_KEY_CHECKS = 0;
-- ... bulk load ...
SET FOREIGN_KEY_CHECKS = 1;

Disable checks for the load, then re-enable. One important note: re-enabling doesn't retroactively validate existing rows. If you insert bad data while checks are off, you'll end up with orphaned references. Always verify data integrity before disabling checks, and consider running a manual consistency check after re-enabling if you're not certain about the input data.

On the read side, foreign key indexes on child columns also speed up JOIN queries. MySQL can use those indexes to efficiently look up related rows. The index overhead from foreign keys often pays for itself in query performance, especially in heavily normalized schemas. See the database normalization guide for when normalization actually helps vs when it adds unnecessary joins.

Common Mistakes

• Mismatched data types. The child column and parent column must match exactly, including sign. An INT UNSIGNED primary key requires an INT UNSIGNED foreign key. Plain INT fails. Check the types in your MySQL data types reference when in doubt.
• Not naming your constraints. MySQL generates names automatically, but they're unreadable. Explicit names like fk_orders_user make SHOW CREATE TABLE output clear and let you drop or modify constraints cleanly.
• Skipping explicit indexes on child columns. MySQL creates an index on the foreign key column automatically when you add the constraint, but it's hidden with a generated name. Create it explicitly for clarity and control.
• Using MyISAM. Foreign key constraints are only enforced on InnoDB tables. MyISAM silently ignores them. On older MySQL setups, tables may still default to MyISAM if default_storage_engine wasn't explicitly set.
• Circular dependencies. Two tables that reference each other require careful insert ordering. Use SET NULL on one side, or disable FOREIGN_KEY_CHECKS temporarily during initial setup.

Visualise Foreign Keys Before Writing DDL

For anything beyond a couple of tables, drawing the relationships before writing DDL saves real time. Cascade behaviour and cardinality become immediately obvious in a diagram. Mistakes that would take an hour to debug in raw SQL are visible at a glance as a misplaced arrow or the wrong cardinality symbol.

The crow's foot notation guide explains how to read the cardinality symbols used in ER diagrams. Once you can read those lines, you can design your schema visually and export the correct MySQL DDL directly from the diagram, foreign key constraints included.

If you're working with a larger schema, the MySQL vs PostgreSQL comparison covers engine-level differences that affect how foreign keys behave across databases, which matters if you're targeting both platforms.