Oprava chyby „a foreign key constraint fails" při importu SQL dumpu

Exportujete databázi, pokusíte se ji načíst do čisté instance a MySQL vyhodí:

Nebo v PostgreSQL:

Proč k tomu dochází

Cizí klíč říká, že „každý orders.user_id musí ukazovat na existující users.id“. Když dump vloží tabulku orders před tabulkou users, odkazovaný nadřazený řádek ještě neexistuje a databáze řádek odmítne.

Většina nástrojů pro export zapisuje tabulky abecedně nebo v pořadí vytvoření — nikoli podle závislostí cizích klíčů. Proto se orders (podřízená) často ocitne před users (nadřazená) a import selže.

Řešení 1: Přeuspořádat INSERTy podle závislostí (správná oprava)

Čisté řešení je vložit každou nadřazenou tabulku před její podřízené. To znamená převést cizí klíče na graf závislostí a načítat tabulky v topologickém pořadí: users → orders → order_items.

Dělat to ručně v souboru o velikosti mnoha gigabajtů je utrpení — musíte vysledovat každý FOREIGN KEY napříč příkazy CREATE TABLE a ALTER TABLE a vyjímat a vkládat obrovské bloky INSERT ve správném pořadí.

Řešení 2: Vypnout kontrolu cizích klíčů během importu

U kompletního a již konzistentního dumpu můžete během načítání validaci bezpečně přeskočit. V MySQL soubor obalte:

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- ... všechny vaše příkazy INSERT ...
SET FOREIGN_KEY_CHECKS = 1;

V PostgreSQL odložte omezení uvnitř transakce:

-- PostgreSQL
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- ... všechny vaše příkazy INSERT ...
COMMIT;

Poté kontroly znovu zapněte, aby zůstaly budoucí zápisy validovány. Funguje to, ale pouze zamaskuje problém s pořadím — pokud je dump částečný, můžete stále skončit s osiřelými řádky.

Řešení 3: Obojí automaticky s DumpCleaner

DumpCleaner načte dump, extrahuje každý cizí klíč z CREATE TABLE a ALTER TABLE, sestaví graf závislostí a vyexportuje čistý soubor, který se prostě naimportuje:

1. Přetáhněte svůj .sql dump do DumpCleaner (MySQL, MariaDB, PostgreSQL, SQLite nebo MS SQL).
2. Zvolte Pořadí INSERTů → Podle závislostí (podle cizích klíčů). Nadřazené tabulky se automaticky umístí před podřízené pomocí topologického řazení.
3. Volitelně zaškrtněte Vypnout kontrolu cizích klíčů — výstup se obalí správným příkazem pro vaši databázi.
4. Exportujte. Naimportujte nový soubor — chyba omezení je pryč.

Dokonce detekuje cyklické cizí klíče a upozorní vás, místo aby vytvořil nemožné pořadí.

Časté dotazy