MySQL Error 1821: Missing Index in Foreign Table

MySQL Error 1821 (HY000, ER_FK_NO_INDEX_CHILD) means MySQL cannot find the required index in the “foreign table” named by the error. In a foreign-key definition, the foreign table is the child (referencing) table. Error 1822 is the corresponding missing-index error for the referenced parent table. See the MySQL 8.4 error reference.

Check the child-table index

MySQL requires an index on the child table whose leading columns match the foreign-key columns in the same order. InnoDB automatically creates a suitable child index when one is needed, so first inspect the live table definition before adding another index:

SHOW CREATE TABLE departments\G
SHOW CREATE TABLE employees\G
SHOW INDEX FROM employees;

The SHOW CREATE TABLE output also shows each table’s storage engine. Parent and child tables must use the same engine that supports foreign keys, such as InnoDB. Adding an index does not make a foreign key valid across mismatched or unsupported engines.

For a single-column foreign key on department_id, an index beginning with department_id is suitable. For a composite foreign key on (tenant_id, department_id), the child index must begin with (tenant_id, department_id) in that order. An index beginning with (department_id, tenant_id) does not match that foreign-key column order.

You can inspect the child table’s index columns and order with INFORMATION_SCHEMA.STATISTICS:

SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employees'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

If no suitable index exists, add a non-unique index that starts with the child foreign-key columns. It does not need to be UNIQUE, because multiple child rows can reference the same parent row:

ALTER TABLE employees
  ADD INDEX ix_employees_department_id (department_id);

For a composite foreign key, define the index with the same leading-column order as the constraint. Do not add a duplicate index if an existing index already starts with the required columns.

Keep Error 1821 distinct from related foreign-key errors

Error 1821 names a missing index in the child (foreign) table. Error 1822 names a missing index in the parent (referenced) table; see how to fix MySQL Error 1822. Error 3780 means the paired child and parent columns have incompatible definitions, while Error 1452 means a child row has no matching parent row; see how to fix MySQL Error 1452. For the generic Error 1215 message, see the foreign-key definition troubleshooting guide. Browse all MySQL error troubleshooting guides.

If SHOW CREATE TABLE already shows a suitable child index but Error 1821 persists, confirm that the constraint and index belong to the same child table and database, then inspect the latest InnoDB diagnostic with SHOW ENGINE INNODB STATUS\G.