SQLダンプのインポート時に発生する「a foreign key constraint fails」を修正する

データベースをエクスポートし、新しいインスタンスに読み込もうとすると、MySQL が次のエラーを吐きます:

あるいは PostgreSQL では:

なぜこれが起きるのか

外部キーは「すべての orders.user_id は既存の users.id を指していなければならない」と定めます。ダンプが users より前に orders を挿入すると、参照先の親行がまだ存在せず、データベースはその行を拒否します。

ほとんどのダンプツールはテーブルをアルファベット順か作成順で書き出します。外部キーの依存順ではありません。そのため orders（子）がしばしば users（親）より前に来てしまい、インポートが失敗します。

解決策1: 依存関係に従って INSERT を並べ替える（正しい修正）

きれいな解決策は、すべての親テーブルをその子より前に挿入することです。つまり、外部キーを依存関係グラフに解決し、トポロジカル順序でテーブルを読み込みます: users → orders → order_items。

数ギガバイトのファイルでこれを手作業でやるのは苦痛です。CREATE TABLE と ALTER TABLE にまたがるすべての FOREIGN KEY を追跡し、巨大な INSERT ブロックを正しい順序で切り貼りしなければなりません。

解決策2: インポート中に外部キーチェックを無効化する

完全ですでに整合性の取れたダンプであれば、読み込み中の検証を安全にスキップできます。MySQL では、ファイルを次のように囲みます:

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- ... すべての INSERT 文 ...
SET FOREIGN_KEY_CHECKS = 1;

PostgreSQL では、トランザクション内で制約を遅延させます:

-- PostgreSQL
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- ... すべての INSERT 文 ...
COMMIT;

その後は今後の書き込みが検証され続けるよう、チェックを再度有効にしてください。これは機能しますが、順序の問題を覆い隠しているだけです。ダンプが部分的な場合は、孤立した行が残ってしまうこともあります。

解決策3: DumpCleaner で両方を自動的に行う

DumpCleaner はダンプを読み込み、CREATE TABLE と ALTER TABLE からすべての外部キーを抽出し、依存関係グラフを構築して、そのままインポートできるクリーンなファイルを書き出します:

1. .sql ダンプを DumpCleaner にドラッグします（MySQL、MariaDB、PostgreSQL、SQLite、MS SQL に対応）。
2. INSERT 順序 → 依存関係順（外部キー対応）を選びます。トポロジカルソートにより、親が子の前に自動配置されます。
3. 必要なら外部キーチェックを無効化にチェックを入れます。お使いのデータベースに合った文で出力を囲みます。
4. エクスポートします。新しいファイルをインポートすると、制約エラーは消えています。

循環する外部キーも検出し、不可能な順序を生成する代わりに警告してくれます。

よくある質問