<p>MySQL throws error 1821 ER_FK_NO_INDEX_CHILD when you try to create a foreign key on columns that are not covered by an index in the child table.</p>
<p>MySQL Error 1821 ER_FK_NO_INDEX_CHILD occurs because the child table lacks an index on the columns referenced by a new foreign key. Create an index matching the foreign key columns, then rerun the ALTER TABLE statement to resolve the issue.</p>
Failed to add the foreign key constaint. Missing index
Error 1821 appears when MySQL cannot create a foreign key because the child table does not have an index on the referenced columns. MySQL requires an index to enforce referential integrity efficiently.
The server responds with Failed to add the foreign key constraint. Missing index for constraint '%s' in the foreign table '%s'. The message pinpoints the constraint name and the offending table.
During insert, update, or delete operations, MySQL must quickly locate related rows in the child table. Without an index, each modification would require a full table scan, degrading performance. By enforcing the index rule, MySQL protects both speed and data integrity.
The most common cause is defining a FOREIGN KEY without first creating an index that covers the same columns in the child table. MySQL 5.6 and later can create the index automatically if you add the FOREIGN KEY in CREATE TABLE, but it cannot when using ALTER TABLE ADD CONSTRAINT if the index is missing.
Another trigger is mismatched column order or data types between the index and the foreign key definition, leading MySQL to treat the existing index as incompatible.
Create an index on the child table that matches the foreign key columns exactly. Then rerun your ALTER TABLE statement. If a partial index exists, drop it and recreate a composite index with the correct column order.
Always verify that the child and parent columns share identical data types and lengths, including signedness, character set, and collation for string columns.
When normalizing databases, engineers often retrofit foreign keys onto legacy tables. Adding the correct composite index before the constraint resolves the issue quickly.
In ORMs, schema migration scripts may omit the index for readability. Edit the migration to include an explicit CREATE INDEX or add USING BTREE in the same ALTER TABLE command.
Design tables with foreign keys and indexes together during the initial modeling phase. Use the same column order in both definitions and adopt a naming convention that pairs each FK with its supporting index.
Automate schema validation with tools such as pt-online-schema-change or Galaxy schema checks, which alert you to missing indexes before deployment.
Error 150 Cannot create foreign key constraint occurs when child and parent column definitions differ. Align data types to resolve it.
Error 1005 Can't create table often masks Error 1821. Check SHOW ENGINE INNODB STATUS for the inner cause and follow the same indexing fix.
The child table has no composite index on all columns referenced by the foreign key.
An index exists but orders the referenced columns differently from the foreign key definition.
An index covers some but not all columns in the FOREIGN KEY clause, rendering it unusable.
Column lengths or signedness differ between index and foreign key, causing MySQL to reject the index.
Occurs when column definitions differ between parent and child tables. Align data types to fix.
A wrapper error that often signals hidden foreign key problems such as missing indexes. Inspect InnoDB status for details.
Similar to 1821 but occurs on the parent table when the referenced columns lack a unique index.
Yes. MySQL requires an index on the child columns to maintain referential integrity. However, a single composite index can satisfy multiple keys if column order matches.
MySQL auto creates the index during CREATE TABLE if it is missing, but it does not when using ALTER TABLE ADD CONSTRAINT. Add it manually in existing tables.
InnoDB only supports BTREE indexes for foreign keys. Specify USING BTREE explicitly for clarity, especially with older versions.
Galaxy's schema aware AI suggests required indexes when you write or refactor ALTER TABLE statements, preventing Error 1821 before you run migrations.
.avif)
.avif){kind=logo}
