Kaip ištaisyti „a foreign key constraint fails“ importuojant SQL išklotinę

Eksportuojate duomenų bazę, bandote ją įkelti į naują serverį, o MySQL išmeta:

Arba PostgreSQL atveju:

Kodėl taip nutinka

Išorinis raktas sako: „kiekvienas orders.user_id turi rodyti į egzistuojantį users.id.“ Kai išklotinė įterpia orders anksčiau nei users, nurodyta pirminė eilutė dar neegzistuoja, ir duomenų bazė atmeta eilutę.

Dauguma išklotinių įrankių lenteles rašo abėcėlės tvarka arba sukūrimo tvarka — bet ne pagal išorinių raktų priklausomybių tvarką. Todėl orders (antrinė) dažnai atsiduria prieš users (pirminę), ir importas nutrūksta.

1 sprendimas: pertvarkyti INSERT pagal priklausomybes (teisingas sprendimas)

Švarus sprendimas — kiekvieną pirminę lentelę įterpti prieš jos antrines lenteles. Tai reiškia išorinius raktus paversti priklausomybių grafu ir lenteles įkelti topologine tvarka: users → orders → order_items.

Daryti tai rankomis kelių gigabaitų faile — kančia: tenka atsekti kiekvieną FOREIGN KEY per CREATE TABLE ir ALTER TABLE sakinius bei iškirpti ir įklijuoti didžiulius INSERT blokus teisinga tvarka.

2 sprendimas: išjungti išorinių raktų tikrinimą importo metu

Jei išklotinė yra pilna ir jau nuosekli, tikrinimą galima saugiai praleisti įkėlimo metu. MySQL atveju apgaubkite failą:

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- ... visi jūsų INSERT sakiniai ...
SET FOREIGN_KEY_CHECKS = 1;

PostgreSQL atveju atidėkite apribojimus tranzakcijos viduje:

-- PostgreSQL
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- ... visi jūsų INSERT sakiniai ...
COMMIT;

Po to vėl įjunkite tikrinimą, kad būsimi įrašymai liktų patikrinti. Tai veikia, tačiau tik paslepia eiliškumo problemą — jei išklotinė yra dalinė, vis tiek galite likti su „našlaitėmis“ eilutėmis.

3 sprendimas: padaryti abu automatiškai su DumpCleaner

DumpCleaner perskaito išklotinę, iš CREATE TABLE ir ALTER TABLE ištraukia kiekvieną išorinį raktą, sudaro priklausomybių grafą ir eksportuoja švarų failą, kuris tiesiog importuojasi:

1. Nutempkite savo .sql išklotinę į DumpCleaner (MySQL, MariaDB, PostgreSQL, SQLite ar MS SQL).
2. Pasirinkite INSERT tvarka → Pagal priklausomybes (atsižvelgiant į FK). Pirminės lentelės automatiškai išdėstomos prieš antrines, naudojant topologinį rūšiavimą.
3. Norėdami, pažymėkite Išjungti išorinių raktų tikrinimą — tuomet išvestis apgaubiama teisingu jūsų duomenų bazės sakiniu.
4. Eksportuokite. Importuokite naują failą — apribojimo klaidos nebėra.

Programa netgi aptinka ciklinius išorinius raktus ir įspėja jus, užuot sukūrusi neįmanomą tvarką.

Dažnai užduodami klausimai