MySQL refuses a foreign key because it cannot open the parent table
ERROR 1824 (HY000): Failed to open the referenced table 'parent'Short answer
Error 1824 means the server could not open the table named in REFERENCES. Either that table does not exist yet, its name differs in letter case on a case-sensitive server, or it is not a plain InnoDB table. Check the parent first: its name, its engine, and whether it was created before the child.
Fix it fast
1Find out which cause you have
Ask the server whether the parent table exists under exactly that name, and which engine it uses. The table name in the error message is the one to look for.
SHOW TABLES LIKE 'parent'; SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'parent';2Match what you see, then jump to its fix
- 1
The first query returns no row, or the error appears while a schema script runs top to bottomThe parent table is created after the child, or not at allFix - 2
The same script works on a Windows or macOS laptop and fails on a Linux serverThe parent name has a different letter case than the real tableFix - 3
The parent exists with the right name, but its engine is not InnoDB, or it is a temporary or partitioned tableThe parent table cannot take part in a foreign keyFix
- 1
What does the error look like?
ERROR 1824 (HY000): Failed to open the referenced table 'parent'Failed to open the referenced table
Cause 1 of 3
The parent table is created after the child, or not at all
1Check
Find the CREATE TABLE of the parent in your script or migrations and see where it sits. A child that points at a parent which does not exist yet fails with this error. A dump that is reloaded in a different order than the foreign keys need is the same situation.
grep -n -i -E "create table .*(parent|child)" schema.sql2Fix
Create the parent first. If the script cannot be reordered, switch foreign key checks off for the session while it runs and switch them back on afterwards. The manual warns that rows added while the checks are off are not verified when you turn them on again, so use this for schema loading, not for data changes.
CREATE TABLE parent (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE child (
id INT NOT NULL PRIMARY KEY,
parent_id INT NOT NULL,
FOREIGN KEY (parent_id) REFERENCES parent (id)
) ENGINE=InnoDB;
-- only when the order cannot be changed:
SET foreign_key_checks = 0;
SOURCE schema.sql;
SET foreign_key_checks = 1;3Verify
Run the statement again and list the foreign keys on the child table.
SHOW CREATE TABLE child\GConfirmed by dev.mysql.com
Cause 2 of 3
The parent name has a different letter case than the real table
1Check
Table names follow the file system. They are case-sensitive on most Unix systems and not on Windows. Compare the name in REFERENCES with what SHOW TABLES prints, and read the server's lower_case_table_names value.
SHOW TABLES;
SHOW VARIABLES LIKE 'lower_case_table_names';2Fix
Write the name in REFERENCES exactly as the table was created. For new projects choose one convention, such as all lowercase, and keep it everywhere. The variable can only be set while the server is being initialized, so changing it later is not an option.
-- the table was created as `Users`, so the reference must say `Users`
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES `Users` (id);3Verify
The ALTER TABLE or CREATE TABLE completes without an error.
SHOW CREATE TABLE orders\GConfirmed by dev.mysql.com
Cause 3 of 3
The parent table cannot take part in a foreign key
1Check
The manual requires parent and child to use the same storage engine, and neither may be a temporary table. InnoDB does not support foreign keys on tables with user-defined partitioning, parent or child. Look at the engine column from the first step, and at the partitioning of the table.
SELECT TABLE_NAME, ENGINE, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('parent', 'child');2Fix
Make both tables InnoDB, regular and unpartitioned. Converting a table that is not yet in any foreign key relationship is a plain ALTER TABLE.
ALTER TABLE parent ENGINE=InnoDB;3Verify
Both tables now report InnoDB, and adding the constraint succeeds.
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('parent', 'child');Confirmed by dev.mysql.com
How do I stop it coming back?
- Keep schema files and migrations in dependency order: referenced tables first, tables that point at them afterwards.
- Pick lowercase table names for every new schema so a move between operating systems never changes their meaning.
- Name the engine in every CREATE TABLE of a schema that uses foreign keys.
- Reload dumps made by mysqldump as they are: the tool switches foreign key checks off for the dump itself.
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: 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'
Error 1822 names the referenced (parent) table, not the child.
2 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 fix