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.

Источник