Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses a foreign key because it cannot open the parent table

ErrorERROR 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.

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

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The 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. 3The 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

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.sql

2Fix

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\G

Confirmed by dev.mysql.com

Did this fix it?

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\G

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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:

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.