Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Adding a foreign key fails: missing index in the referenced table

ErrorError Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'fk_orders_customer' in the referenced table 'customers'

Short answer

Error 1822 names the referenced (parent) table, not the child. MySQL needs an index on the parent where the referenced columns are the first columns, in the same order as in the FOREIGN KEY. Create a primary key, unique key or index on those columns first, then add the constraint again.

  • Applies to MySQL 8.4, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the table name at the end of the message; it is the parent. List every index on it and compare with the columns your REFERENCES clause names.

    SHOW INDEX FROM customers;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW INDEX on the parent has no row for the column, for example a business key such as email or code.The referenced columns have no index at allFix
    2. 2SHOW INDEX lists the columns, but Seq_in_index puts the referenced column after another one, or the order differs from your FOREIGN KEY.A composite index exists but starts with a different columnFix

What does the error look like?

  • Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'fk_orders_customer' in the referenced table 'customers'

Cause 1 of 2

The referenced columns have no index at all

1Check

The foreign key points at a column that is not the primary key and was never indexed. This often happens in a script that creates the child before the parent has its index.

SHOW INDEX FROM customers WHERE Column_name = 'customer_code';

2Fix

The manual says to create the index on the referenced columns before adding the constraint with ALTER TABLE. A unique index is the right kind for a key that identifies one parent row.

ALTER TABLE customers ADD UNIQUE KEY uq_customer_code (customer_code);
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_code) REFERENCES customers (customer_code);

3Verify

The ALTER TABLE finishes, and the constraint shows in the table definition.

SHOW CREATE TABLE orders\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

A composite index exists but starts with a different column

1Check

In the referenced table the index must have the referenced columns as its first columns, in the same order. An index on (region, code) does not serve a key that references (code, region).

SHOW INDEX FROM customers;

2Fix

Add an index whose leading columns match the foreign key list exactly, or reorder the columns in the FOREIGN KEY so they match the existing index.

ALTER TABLE customers ADD UNIQUE KEY uq_code_region (code, region);
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (code, region) REFERENCES customers (code, region);

3Verify

Run the same ALTER TABLE again; it now completes.

SHOW CREATE TABLE orders\G

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Create or alter the parent table, with its key, before any table that references it.
  • Reference primary or unique keys; MySQL deprecates foreign keys on non-unique or partial keys.
  • In schema-sync tools, check that the generated script orders the index before the constraint.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

Adding a foreign key fails because the two columns do not match

ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'fk_orders_user' are incompatible.

Error 3780 means the column in the child table and the column it references are defined differently.

2 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.