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
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
-
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
1062Duplicate entry for key1061Duplicate key name1452Foreign key fails: cannot add child row1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1217Cannot delete or update a parent row1824Failed to open the referenced table1048Column cannot be null
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.
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.