<p>MySQL throws error 1822 when you try to create a foreign key referencing columns that lack a supporting index on the parent table.</p>
<p>MySQL Error 1822 ER_FK_NO_INDEX_PARENT happens when you add a foreign key but the referenced columns in the parent table have no index. Add a matching index or primary key on those columns, then re-run the ALTER TABLE statement.</p>
Failed to add the foreign key constaint. Missing index
Error 1822 appears when MySQL cannot add a foreign key because the referenced columns in the parent table are not indexed. The engine needs an index to enforce referential integrity efficiently.
The message usually reads: Failed to add the foreign key constraint. Missing index for constraint '%s' in the referenced table '%s'.
The parent table lacks a primary key or unique index that exactly matches the column list used in the child table's FOREIGN KEY definition. MySQL demands this index to locate parent rows quickly.
Mismatched data types, column order differences, or referencing a non-unique index can also trigger the error because MySQL treats them as missing or incompatible indexes.
Create a primary key or unique index on the referenced columns before adding the foreign key. Ensure column data types and ordering match the child table definition.
After indexing, rerun ALTER TABLE ... ADD CONSTRAINT to establish the relationship. Validate with SHOW CREATE TABLE to confirm the foreign key exists.
During schema migrations, developers often add child tables first. Always create or alter the parent table to include required indexes before adding constraints.
When splitting composite keys, make sure every referenced subset has its own unique index if child tables point to only part of the composite.
Design schemas with primary keys on parent tables from the outset. Use descriptive naming conventions so indexes are obvious to teammates.
Automate schema linting in Galaxy or CI pipelines to detect foreign keys lacking corresponding indexes before deployment.
Error 1215 Cannot add foreign key constraint occurs for multiple reasons, including missing indexes; check that error if 1822 does not appear.
Error 1831 Duplicate foreign key name signals you are reusing a constraint identifier; rename or drop the existing foreign key first.
The parent table has no primary key, so no index exists on the referenced column set.
An index exists but is not unique, and MySQL requires a primary or unique index for referential integrity.
The parent index covers the right columns but in a different order than the foreign key definition.
A generic foreign key failure covering multiple issues, including engine mismatch and missing indexes.
The new constraint name already exists in the schema; choose a unique identifier.
Occurs at runtime when inserting rows that have no matching parent keys.
Yes. The parent table must have a UNIQUE or PRIMARY KEY index on the referenced columns for the foreign key to work.
No. The foreign key column list must match the parent index column list in number and order.
Additional indexes add slight overhead on insert but are necessary for referential integrity. Proper indexing often speeds up joins.
Galaxy highlights missing indexes in real time and offers one-click fixes, preventing the error before you run migrations.
.avif)
.avif){kind=logo}
