Adding a foreign key fails: missing index in the referenced table
Error 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
SHOW 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
SHOW 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
- 1
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\GConfirmed by dev.mysql.com
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\GConfirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses to create a foreign key (error 1215)
ERROR 1005 (HY000): Can't create table (errno: 150 "Foreign key constraint is incorrectly formed")
Error 1215 is a generic label.
5 causes, each with a fixDatabases
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 fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix