// MySQL error reference

MySQL error 1215: Cannot add foreign key constraint

MySQL rejected a FOREIGN KEY definition: the column types differ, the parent isn't indexed, or the table engine doesn't support foreign keys.

Error number
1215
Name
ER_CANNOT_ADD_FOREIGN
SQLSTATE
HY000
Category
Keys and constraints
Error message
Cannot add foreign key constraint

What it means

1215 says the foreign key couldn't be created but not why. MySQL 8.0 often reports a more specific error instead (3780 for incompatible columns, 1822 for a missing parent index, 1824 for a missing parent table). For the details, run SHOW ENGINE INNODB STATUS and read the LATEST FOREIGN KEY ERROR section.

Common causes

  • The child and parent columns have different types, signedness (INT versus INT UNSIGNED), length, or character set and collation.
  • The referenced columns aren't a primary key or don't start an index.
  • One of the tables uses MyISAM or another engine without foreign key support.
  • The parent table doesn't exist yet when the child is created.
  • ON DELETE SET NULL on a column declared NOT NULL.

How to fix it

  1. Read the detailed reason.

    SHOW ENGINE INNODB STATUS;  -- LATEST FOREIGN KEY ERROR
  2. Compare the column definitions on both sides and make them identical.

    SHOW CREATE TABLE customers;
    SHOW CREATE TABLE orders;
  3. Make the column types match, then add the key.

    ALTER TABLE orders MODIFY customer_id INT UNSIGNED NOT NULL;
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id) REFERENCES customers (id);
  4. Convert MyISAM tables to InnoDB.

    ALTER TABLE customers ENGINE = InnoDB;

Related errors

More keys and constraints errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.