Novērst “a foreign key constraint fails” SQL izgāztuves importēšanā

Tu eksportē datubāzi, mēģini to ielādēt jaunā instancē, un MySQL izmet:

Vai arī PostgreSQL:

Kāpēc tas notiek

Ārējā atslēga nosaka, ka “katram orders.user_id jānorāda uz esošu users.id.” Kad izgāztuve ievieto orders pirms users, norādītā vecāka rinda vēl neeksistē, un datubāze rindu noraida.

Vairums izgāztuves rīku raksta tabulas alfabētiskā secībā vai izveides secībā — nevis ārējo atslēgu atkarību secībā. Tāpēc orders (bērns) bieži nonāk pirms users (vecāks), un imports sabojājas.

Risinājums 1: Pārkārtot INSERT pēc atkarībām (pareizais risinājums)

Tīrs risinājums ir ievietot katru vecāka tabulu pirms tās bērniem. Tas nozīmē atrisināt ārējās atslēgas atkarību grafā un ielādēt tabulas topoloģiskā secībā: users → orders → order_items.

Darīt to ar rokām daudzu gigabaitu failā ir sāpīgi — jāizseko katra FOREIGN KEY visos CREATE TABLE un ALTER TABLE priekšrakstos un jāizgriež/jāielīmē milzīgi INSERT bloki pareizā secībā.

Risinājums 2: Atspējot ārējo atslēgu pārbaudes importa laikā

Pilnai, jau konsekventai izgāztuvei validāciju ielādes laikā var droši izlaist. MySQL gadījumā ietin failu:

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- ... visi tavi INSERT priekšraksti ...
SET FOREIGN_KEY_CHECKS = 1;

PostgreSQL gadījumā atliec ierobežojumus transakcijā:

-- PostgreSQL
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- ... visi tavi INSERT priekšraksti ...
COMMIT;

Pēc tam atkal ieslēdz pārbaudes, lai turpmākie ieraksti paliktu validēti. Tas darbojas, taču tikai maskē secības problēmu — ja izgāztuve ir daļēja, joprojām var rasties bāreņu rindas.

Risinājums 3: Izdari abus automātiski ar DumpCleaner

DumpCleaner nolasa izgāztuvi, izvelk katru ārējo atslēgu no CREATE TABLE un ALTER TABLE, izveido atkarību grafu un eksportē tīru failu, kas vienkārši importējas:

1. Ievelc savu .sql izgāztuvi DumpCleaner (MySQL, MariaDB, PostgreSQL, SQLite vai MS SQL).
2. Izvēlies INSERT secība → Pēc atkarībām (FK-apzinīga). Vecāki tiek novietoti pirms bērniem automātiski, izmantojot topoloģisko kārtošanu.
3. Pēc izvēles atzīmē Atspējot ārējo atslēgu pārbaudes — tas ietin izvadi ar pareizo priekšrakstu tavai datubāzei.
4. Eksportē. Importē jauno failu — ierobežojuma kļūda ir pazudusi.

Tas pat nosaka cikliskas ārējās atslēgas un brīdina tevi, nevis rada neiespējamu secību.

Biežāk uzdotie jautājumi