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

说明与用途

在恢复过程中，删除并重新创建表可能导致父表丢失，并使子表出现悬空外键，因为父表在重新创建时缺少必要的唯一键。

问题

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.

结果

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.

工作流

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

证据

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

来源

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

来源

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

来源