The restore that dropped our staging tables: a foreign key nobody restored

Description and use

A restore that drops and recreates tables can lose a parent table and leave a child table with a dangling foreign key if the parent is recreated without its required unique key.

Problem

A restore that drops and recreates tables can leave the target database in a partially restored state if a foreign key references a table that is recreated without its required unique key.

Outcome

The restore failed to preserve the parent table and left a dangling foreign key, demonstrating that drop‑and‑recreate restores can have a large blast radius if foreign keys reference tables outside the restore set.

Workflow

• 1. Restore dump using TunnelForge in drop‑and‑recreate mode.
• 2. Drop all selected tables (including the parent).
• 3. Create each table one by one and load data.
• 4. On first error (missing unique key for FK), stop without rollback.
• 5. Remaining tables that had not yet been recreated are lost.
• 6. Result: parent table missing, child table with dangling FK.

Evidence

ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint '...' in the referenced table '...'

Source

The restore stopped partway with this error: ... The import mode I used had already dropped every selected table up front.

Source

If you put the UNIQUE KEY inside the CREATE TABLE, the same sequence succeeds and audit_log keeps its row.

Source