MySQL Error 1822: Missing Index in Referenced Table

MySQL Error 1822 (HY000, ER_FK_NO_INDEX_PARENT) means MySQL cannot find a suitable index for the referenced columns in the parent table. The error message says the foreign-key constraint is missing an index in the “referenced table.” This happens when MySQL creates or alters a foreign key; it is not an error about a child row’s data. See the MySQL 8.4 error reference.

Example: the parent column is not indexed

Here, the child table defines an index on department_id, but the referenced column in departments has no index. MySQL cannot use it as a parent key:

CREATE TABLE departments (
  department_id INT UNSIGNED NOT NULL,
  department_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE employees (
  employee_id BIGINT UNSIGNED NOT NULL,
  department_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (employee_id),
  KEY ix_employees_department_id (department_id),
  CONSTRAINT fk_employees_department
    FOREIGN KEY (department_id)
    REFERENCES departments (department_id)
) ENGINE = InnoDB;

Add the right index to the parent table

First decide what the referenced columns mean in the data model. If department_id uniquely identifies a department, make it a primary key or add a UNIQUE index. Before adding a unique key to an existing table, check for duplicates:

SELECT department_id, COUNT(*) AS row_count
FROM departments
GROUP BY department_id
HAVING COUNT(*) > 1;

If the column is meant to be a unique candidate key and the values satisfy that rule, add a unique index:

ALTER TABLE departments
  ADD UNIQUE INDEX uq_departments_department_id (department_id);

Use a primary key instead when department_id is the table’s actual primary identifier and meets the primary-key requirements. If you plan to make it a primary key, check for NULL values as well as duplicates. Do not add a unique index simply to silence the error if duplicate parent values are valid; choose a different referenced key that represents one parent row.

For a multi-column foreign key, the referenced index must start with the referenced columns in the same order. For example, a foreign key that references (tenant_id, department_id) needs a parent index such as (tenant_id, department_id). An index on (department_id, tenant_id) does not have the same leading-column order.

Inspect the parent definition and indexes before retrying the constraint:

SHOW CREATE TABLE departments\G
SHOW INDEX FROM departments;

MySQL requires the referenced columns to be the leading columns of a parent index in the same order. In MySQL 8.4, referencing a non-unique InnoDB key is deprecated and requires a compatibility setting; a missing unique parent key can produce Error 6125. Prefer a primary or unique key for new schemas. See the MySQL foreign-key index requirements.

Error 1822 versus Error 1821

Both errors concern indexes, but the message identifies which table is missing one. In MySQL 8.4, Error 1822 names the referenced table (the parent); Error 1821 names the foreign table (the child). InnoDB creates a child-side index automatically when one is needed, but that does not create the required index on the parent key. Check the parent first when the message says Error 1822; for the child-side case, see how to fix MySQL Error 1821.

Error 1822 is also different from Error 3780, which identifies incompatible child and parent column definitions, and Error 1452, which means a child row has no matching parent row. For that data error, see how to fix MySQL Error 1452. For the generic foreign-key definition error found in some MySQL versions, see how to fix MySQL Error 1215. For incompatible columns, see how to fix MySQL Error 3780.