// MySQL error reference

MySQL error 1822: Missing index for foreign key constraint

The referenced columns in the parent table aren't indexed in a way the foreign key can use. Reference the primary key or add an index.

Error number
1822
Name
ER_FK_NO_INDEX_PARENT
SQLSTATE
HY000
Category
Keys and constraints
Error message
Failed to add the foreign key constraint. Missing index for constraint '%s' in the referenced table '%s'

What it means

InnoDB needs an index on the referenced columns, with those columns first and in the same order, so it can check parent rows quickly. If there isn't one, creating the foreign key fails with 1822, which names the constraint and parent table.

Common causes

  • Referencing a column that isn't the primary key and has no index.
  • A composite foreign key whose column order doesn't match any index on the parent.

How to fix it

  1. Add a unique index (or primary key) on the referenced columns, then create the foreign key.

    ALTER TABLE customers ADD UNIQUE INDEX ux_customers_code (code);
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_code
      FOREIGN KEY (customer_code) REFERENCES customers (code);

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.