本文へ移動
ImportFlow
日本語
Englishالعربية日本語Português (Brasil)Español
早期アクセスを申し込む

技術ガイド

PostgreSQLで外部キーを含むCSVデータをインポートする

入力データの参照を、推測せずに取り込み先のIDへ解決します。テナントの範囲、一致する参照先の欠落、取り込み順、NULLを許容する関係や制約の検査を遅延する関係を扱います。

Mohamed Atef · ImportFlow
最終レビュー

入力データの参照値をステージングに読み込み、それぞれを承認済みの取り込み先キーに照合します。必要なすべての参照について、許可された一致先がちょうど1件あることを確認してから挿入します。外部キーは、取り込み先の参照先が存在することを検査します。顧客の旧システムの「Company ID」が、自社の会社テーブルの主キーと同じ意味かどうかは判断できません。

ファイルにすでに信頼できる取り込み先IDが含まれていれば、解決は簡単かもしれません。それでも、参照先が認可されたテナントに属し、操作者がその参照先にレコードを関連付けてよいことを確認する必要があります。旧システムでIDが独立して生成されていた場合は、そのまま外部キーフィールドにコピーせず、外部参照として保持してください。

対象範囲と前提条件. PostgreSQL 17を対象とします。例では1つのセッション内で、架空のデータを使った一時テーブルを使用します。

照合に使う識別情報を選ぶ

顧客のエクスポートに、連絡先と、old-42のようなcompany_refが含まれるとします。自社の会社テーブルは、独自に生成したIDを割り当てます。old-42を数値型の会社IDにインポートすれば失敗しますが、キャストしたり任意の数値に置き換えたりする方が、さらに問題です。

照合に使う名前空間を、テナント、入力元システム、外部の会社参照で定義します。その名前空間から取り込み先の会社IDへの、レビュー済みの対応表を保存または入手してください。会社名は、重複、変更、表記揺れがあるため、通常は代替として不適切です。メールドメインも、複数の事業体で共有されることがあります。

許可された名前空間内での一致件数意味対応
0件参照先が存在しない、または指定された値が誤っている参照を修正する、承認済みの親レコードを作成する、または除外を記録する
1件候補が一意に存在する権限を確認し、取り込み先のIDを使用する
複数件同一性の判定規則が曖昧停止して識別モデルを解決する。最初の候補を選ばない

「承認済みの親レコードを作成する」は、業務上の操作です。連絡先ファイルにないフィールドが必要になる場合や、その操作固有の副作用が起きる場合があります。意図したアプリケーションの規則でない限り、制約を満たすためだけに仮の会社を自動作成してはいけません。

テナントの範囲内で外部参照を解決する

この一式のセットアップでは、使い捨てのPostgreSQL 17セッション内で一時テーブルを使います。同じ入力元システムで同じ外部参照を持つ、2つのテナントを表します。

SQL
CREATE TEMP TABLE import_companies_demo (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id integer NOT NULL,
  source_system text NOT NULL,
  external_ref text NOT NULL,
  UNIQUE (tenant_id, source_system, external_ref),
  UNIQUE (tenant_id, id)
);

INSERT INTO import_companies_demo
  (tenant_id, source_system, external_ref)
VALUES (10, 'legacy-crm', 'old-42'),
       (20, 'legacy-crm', 'old-42');

CREATE TEMP TABLE import_contacts_stage (
  source_record integer PRIMARY KEY,
  email text NOT NULL,
  company_ref text NOT NULL
);

INSERT INTO import_contacts_stage VALUES
  (1, 'first@example.test', 'old-42'),
  (2, 'second@example.test', 'missing-99');

インポートで認可されたテナントは10、承認済みの入力元システムはlegacy-crmです。実際の実装では、信頼できるジョブの入力値として扱いますが、この例では定数です。連絡先を挿入する前に、参照の解決を確認します。

SQL
SELECT stage.source_record, stage.company_ref,
       count(company.id) AS matches
FROM import_contacts_stage AS stage
LEFT JOIN import_companies_demo AS company
  ON company.tenant_id = 10
 AND company.source_system = 'legacy-crm'
 AND company.external_ref = stage.company_ref
GROUP BY stage.source_record, stage.company_ref
HAVING count(company.id) <> 1
ORDER BY stage.source_record;

結果では、レコード2の一致件数が0件だと分かります。このスキーマでは、会社の名前空間に対する一意制約が複数一致を防ぎます。実際の取り込み先にその制約がない場合でも、同じ診断によって曖昧さを検出できます。一意に照合する規則を信頼できるようにするには、先にその曖昧さを修正する必要があります。

内部結合だけでは、レコード2が黙って落ちてしまいます。その出力は、連絡先1件のインポートに成功したように見えるかもしれません。明示的な診断を行うことで、除外をSQLの偶発的な副作用ではなく、判断の結果にできます。

制約にテナントの関係を含める

この事前検証では、データの管理責任者が、レコード2をold-42に修正することを承認したとします。これは架空のデータに対する修正であり、参照先が見つからない場合の汎用的な代替処理ではありません。次のブロックはロールバックのみで終える事前検証です。SELECTの結果を使って、解決後のレコードを確認してください。

SQL
BEGIN;

UPDATE import_contacts_stage
SET company_ref = 'old-42'
WHERE source_record = 2;

CREATE TEMP TABLE import_contacts_target (
  tenant_id integer NOT NULL,
  source_record integer NOT NULL,
  email text NOT NULL,
  company_id bigint NOT NULL,
  PRIMARY KEY (tenant_id, source_record),
  FOREIGN KEY (tenant_id, company_id)
    REFERENCES import_companies_demo (tenant_id, id)
);

INSERT INTO import_contacts_target
  (tenant_id, source_record, email, company_id)
SELECT 10, stage.source_record, stage.email,
       (SELECT company.id
        FROM import_companies_demo AS company
        WHERE company.tenant_id = 10
          AND company.source_system = 'legacy-crm'
          AND company.external_ref = stage.company_ref)
FROM import_contacts_stage AS stage;

SELECT * FROM import_contacts_target ORDER BY source_record;
ROLLBACK;

2つのレコードは、テナント10の会社に解決されます。スカラー副問い合わせで一致がない場合はNULLとなり、取り込み先がNOT NULLによって拒否します。複数件が一致すると、スカラー副問い合わせが失敗します。これにより、内部結合で行が黙って失われることを防げます。

複合外部キーは、テナントと会社IDの組み合わせが存在することを検証します。会社IDだけの外部キーでは、全体での存在しか確認できず、テナントの整合性は確認できません。ここでは説明のため入力レコード番号を主キーにしています。再利用可能なインポーターでは、別々のファイルを区別するために、インポート／ジョブの名前空間、または安定した承認済みの業務上の識別情報が必要です。

アプリケーションが認可とRLSに依存する箇所では、それらが引き続き必要です。参照整合性は、完全なアクセス制御システムではありません。

親を先に取り込み、IDの対応表を保持する

親と子の両方をインポートする場合は、まず親の識別情報を利用可能にします。承認済みの親の集合を挿入し、生成されたIDの対応表を取得または解決してから、その対応表で子の参照を変換します。名前空間はテナントごとに限定し、入力元システムに結び付けてください。

生成されるIDが連番になる、CSVの行順と一致する、最初に返されたIDにオフセットを足せば復元できる、と考えてはいけません。並行トランザクション、失敗した処理、シーケンスの動作によって、その前提は崩れます。キーとIDの明示的な対応を使ってください。

一度限りのスクリプトでデータセットが小さければ、トランザクション内でレビュー済みの対応表を保持できます。再開可能な移行には、プロセスの再起動後も残り、再試行で親を重複作成しないようにする、永続的な対応の証拠が必要です。保持とアクセスの方針は、その証拠に含まれる入力データに応じて定めるべきです。

入力データに同じ親を表す複数の表現が含まれる場合は、子を作成する前に重複の解消方法を決めます。ここで、移行作業が単一テーブルのインポートを超えることがあります。IDの値を指定する規則は、IDENTITYとデフォルト値のガイドで別途扱います。

NULL許容、2段階処理、制約の検査を遅延する関係

NULLを許容する外部キーは、正当に「会社の割り当てなし」を表せます。しかし、暗黙に「指定された会社が見つからなかった」を意味してはいけません。入力データのレビューと結果では、この2つを区別してください。

両方の行が存在してから、2回目の処理で参照を埋めることもできます。ただし、取り込み先が一時的なNULLを許可し、アプリケーションがその状態を許容できる場合に限ります。各処理を別々にコミットすると、中間状態が見えるようになり、ワークフローを起動する可能性があります。両方を1つのトランザクションにまとめれば、データベースの状態はコミット時に同時に見えるようになりますが、それでもトリガーと外部への影響を理解する必要があります。

遅延可能な外部キーは、検査を同一トランザクションの後の時点まで延期できます。欠けている参照先の行を作り出したり、列をNULL許容にしたり、コミット時に違反を残したりできるわけではありません。検査時点を遅延できるのは、DEFERRABLEとして宣言された制約だけです。PostgreSQL 17のNOT NULL制約とCHECK制約は、この方法では遅延されません。SET CONSTRAINTSを参照してください。

循環参照には、具体的なスキーマとトランザクションの計画が必要です。有効なNULL状態、検査を遅延する参照、業務モデルの再設計などが選択肢になります。外部キーの無効化は、一般的な復旧手順ではありません。選んだ方法を、実際のアプリケーションの不変条件に照らしてテストしてください。

インポート後に関係を検証する

受け入れたすべての入力レコードの処理結果を確認でき、その解決済み参照がレビュー済みの対応表と一致することを検査します。欠落したレコードと、誤った親に関連付けられたレコードは、別々に数えてください。データベースが有効な参照を強制していても、インポーターが別の有効な会社を誤って選ぶことはあります。

事前照会の結果は古くなることがあります。最終的な制約を維持し、適切なトランザクション戦略でマッピングと書き込みを実行します。親の削除、参照の再割り当て、競合するインポートの扱いを決めてください。管理された一度限りの移行なら、並行実行できる移行サービスを作るより、書き込み時間帯を調整する方が簡単な場合があります。

インポート後の検証で、受け入れた識別情報と重要なフィールドを比較し、その後、アプリケーションの通常の関連表示でも動作を確認します。

出典と関連資料