Korjaa "a foreign key constraint fails" SQL-dumpia tuotaessa

Viet tietokannan, yrität ladata sen tyhjään instanssiin, ja MySQL antaa virheen:

Tai PostgreSQL:ssä:

Miksi näin käy

Viiteavain sanoo: “jokaisen orders.user_id-arvon täytyy osoittaa olemassa olevaan users.id-riviin.” Kun dumppi lisää orders-taulun ennen users-taulua, viitattua yläriviä ei vielä ole olemassa, ja tietokanta hylkää rivin.

Useimmat dumppaustyökalut kirjoittavat taulut aakkosjärjestyksessä tai luontijärjestyksessä — eivät viiteavainten riippuvuusjärjestyksessä. Niinpä orders (lapsi) päätyy usein ennen users-taulua (vanhempi), ja tuonti kaatuu.

Korjaus 1: Järjestä INSERTit riippuvuuksien mukaan (oikea korjaus)

Siisti ratkaisu on lisätä jokainen ylätaulu ennen sen lapsia. Se tarkoittaa viiteavainten ratkaisemista riippuvuusgraafiksi ja taulujen lataamista topologisessa järjestyksessä: users → orders → order_items.

Tämän tekeminen käsin monen gigatavun tiedostossa on tuskallista — sinun on jäljitettävä jokainen FOREIGN KEY CREATE TABLE- ja ALTER TABLE -lauseista ja leikattava/liitettävä valtavia INSERT-lohkoja oikeaan järjestykseen.

Korjaus 2: Poista viiteavaintarkistukset käytöstä tuonnin ajaksi

Täydelliselle, jo valmiiksi eheälle dumpille voit turvallisesti ohittaa validoinnin latauksen aikana. MySQL:ssä kääri tiedosto:

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- ... kaikki INSERT-lauseesi ...
SET FOREIGN_KEY_CHECKS = 1;

PostgreSQL:ssä lykkää rajoitteita transaktion sisällä:

-- PostgreSQL
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- ... kaikki INSERT-lauseesi ...
COMMIT;

Ota tarkistukset takaisin käyttöön jälkeenpäin, jotta tulevat kirjoitukset pysyvät validoituina. Tämä toimii, mutta se vain peittää järjestysongelman — jos dumppi on osittainen, voit silti päätyä orpoihin riveihin.

Korjaus 3: Tee molemmat automaattisesti DumpCleanerilla

DumpCleaner lukee dumpin, poimii jokaisen viiteavaimen CREATE TABLE- ja ALTER TABLE -lauseista, rakentaa riippuvuusgraafin ja vie puhtaan tiedoston, joka tuodaan ilman ongelmia:

1. Vedä .sql-dumppisi DumpCleaneriin (MySQL, MariaDB, PostgreSQL, SQLite tai MS SQL).
2. Valitse INSERT-järjestys → Riippuvuuksien mukaan (viiteavaintietoinen). Vanhemmat sijoitetaan lasten eteen automaattisesti topologisella lajittelulla.
3. Halutessasi rastita Poista viiteavaintarkistukset käytöstä — se kääri tulosteen tietokannallesi oikealla lauseella.
4. Vie. Tuo uusi tiedosto — rajoitevirhe on poissa.

Se jopa tunnistaa kehämäiset viiteavaimet ja varoittaa sinua sen sijaan, että tuottaisi mahdottoman järjestyksen.

Usein kysytyt kysymykset