外部キーが解決する「存在しない参照先」の問題
部品表、顧客と注文、記事とカテゴリのように、実務データは複数の表をIDで結びます。ところが、子側の表へ存在しないIDを保存できる設計では、参照先を削除した瞬間に「材料が不明な部品」「顧客が存在しない注文」が生まれます。この状態が参照切れ、いわゆる孤児レコードです。
SQLiteの外部キー制約は、子レコードが指す親レコードの存在をデータベース自身に検査させる仕組みです。アプリケーション側の事前確認だけに頼らず、書き込みの最終地点で整合性を守れます。ただしSQLiteでは、外部キーを定義しただけでは不十分です。接続ごとに有効化し、NULL、削除時動作、複合キー、遅延制約、索引まで含めて設計する必要があります。
この記事では、材料マスタと部品表を例に、入力から検証、再実行、将来のAPI・CAD連携まで再利用できる外部キー設計を組み立てます。
親キー・子キー・レコードの関係
外部キーには、参照される親キーと、参照する子キーがあります。次の例では、materials.material_idが親キー、parts.material_idが子キーです。
CREATE TABLE materials (
material_id TEXT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE parts (
part_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
material_id TEXT NOT NULL,
FOREIGN KEY (material_id)
REFERENCES materials(material_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
CREATE INDEX parts_material_id_idx
ON parts(material_id);1行が1レコード、列がfield、material_idなどの列名がkey、MAT-001などの中身がvalueです。SQLiteのTEXTは列の型を表します。PRIMARY KEYは親を一意に識別し、FOREIGN KEYは子の値が親に存在することを要求します。
たとえば先に次の親レコードを保存します。
INSERT INTO materials (material_id, name)
VALUES ('MAT-001', 'アルミニウム合金');その後、material_id = 'MAT-001'の部品は登録できます。しかし、存在しないMAT-999を参照する部品は、外部キーが有効なら拒否されます。入力から出力までの流れは、概ね次のようになります。
- CSVやAPIから材料・部品データを受け取る
- 文字コード、必須項目、型、ID形式を検証する
- 親である材料を保存する
- 子である部品を保存する
- 外部キー違反を記録し、失敗したレコードを隔離する
- 整合性が確認できたデータだけをDB、API、CADツールへ渡す
アプリケーションのバリデーションは「入力が契約どおりか」を確認し、外部キーは「保存時点でも参照関係が成立するか」を保証します。役割が異なるため、どちらか一方で代用するのではなく、二層で守るのが基本です。
SQLiteでは接続ごとに外部キーを有効化する
SQLiteは外部キー構文を解釈しても、実行時の検査が無効な接続では違反を止めません。公式文書は、既定値に依存せず、アプリケーションが接続ごとに明示的に設定することを推奨しています。
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;2行目の結果が1なら有効、0なら無効です。重要なのは、PRAGMA foreign_keysをトランザクションの途中で切り替えても効果がない点です。接続直後、BEGINや書き込み処理より前に有効化します。コネクションプールを使う場合も、借り出した各接続で状態を確認できる設計にします。
「開発環境では検査されたが、バッチ接続では無効だった」という差を防ぐには、接続処理を1か所へ集約し、有効化と確認を初期化契約に含めるのが有効です。
NULLと型の意味を先に決める
SQLiteでは、子キーがNULLなら親との一致を要求しません。したがって、次の列では材料未設定の部品を保存できます。
material_id TEXT,
FOREIGN KEY (material_id) REFERENCES materials(material_id)材料が必須の業務なら、外部キーだけでなくNOT NULLが必要です。逆に「設計途中は未確定」という状態を正当に表すならNULLを許し、空文字やUNKNOWNのような疑似値を混ぜない方が意味が明確です。
型についても、IDを数値へ安易に変換しないことが大切です。0012と12が異なる識別子ならTEXTとして保持し、CSV読込時も文字列のまま扱います。IDは計算対象ではなく、表記を含めて一致を判定する識別子だからです。日時や数量のような値は別列へ分離し、単位も列名やマスタで明示します。
親キーは通常PRIMARY KEYにします。別の列を参照する場合は、その列がUNIQUEであり、照合規則も親の定義と一致していなければなりません。人が読む名称や変更されやすい型番ではなく、安定した内部IDを親キーにすると、表記変更の影響を小さくできます。
削除・更新時の動作は業務の寿命に合わせる
親レコードを削除または更新したときの動作は、ON DELETEとON UPDATEで指定します。SQLiteが提供する主なactionは次のとおりです。
| action | 親の変更時の動作 | 適する例 |
|---|---|---|
NO ACTION | 文または制約の検査時に違反を判定する | 既定の厳格な管理 |
RESTRICT | 依存する子があれば直ちに拒否する | 使用中マスタの削除防止 |
CASCADE | 親の更新・削除を子へ伝播する | 親と同じ寿命を持つ明細 |
SET NULL | 子キーをNULLへ変更する | 参照解除を許す任意項目 |
SET DEFAULT | 子キーを既定値へ変更する | 既定値に対応する親が存在する設計 |
材料を使用中の部品があるなら、材料削除はRESTRICTで止める方が安全です。一方、注文と注文行のように明細が親と同じ寿命を持つならON DELETE CASCADEが自然な場合があります。
ただしCASCADEは便利な削除機能ではなく、業務上の所有関係を表す契約です。影響件数を確認せずに使うと、1件の親削除が大量の子削除へ広がります。監査上残すべきデータなら、物理削除ではなくdeleted_atや状態列を使う論理削除も検討します。
SET NULLを選ぶ場合、子列がNOT NULLでは実行できません。SET DEFAULTも、変更後の既定値に対応する親レコードがなければ外部キー違反になります。action名だけでなく、変更後のレコードが全制約を満たすかまで確認します。
複合キーと部品表の参照関係
1列で一意にならないデータでは、複数列を組み合わせた複合キーを使います。たとえば版と部品番号の組で設計を識別するなら、親と子の列数と並びを一致させます。
CREATE TABLE part_revisions (
part_id TEXT NOT NULL,
revision TEXT NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (part_id, revision)
);
CREATE TABLE drawings (
drawing_id TEXT PRIMARY KEY,
part_id TEXT NOT NULL,
revision TEXT NOT NULL,
FOREIGN KEY (part_id, revision)
REFERENCES part_revisions(part_id, revision)
);複合外部キーでは、子キーを構成するいずれかの列がNULLだと親の存在を要求しません。版だけ未入力のレコードを許したくないなら、両方へNOT NULLを付けます。
部品表の親子関係にも外部キーを使えます。
CREATE TABLE bom_lines (
parent_part_id TEXT NOT NULL,
child_part_id TEXT NOT NULL,
quantity REAL NOT NULL CHECK (quantity > 0),
PRIMARY KEY (parent_part_id, child_part_id),
FOREIGN KEY (parent_part_id)
REFERENCES parts(part_id)
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (child_part_id)
REFERENCES parts(part_id)
DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX bom_lines_child_part_id_idx
ON bom_lines(child_part_id);主キーは同じ親子の重複登録を防ぎ、CHECKは数量の正常範囲を守り、2本の外部キーは両端の部品が存在することを保証します。ただし、これだけでは循環参照を禁止できません。AがBを含み、BがAを含む構造を禁止するには、再帰CTEやアプリケーション側のグラフ検査を追加します。外部キーは「存在」を守りますが、関係の全意味までは自動で判断しません。
即時制約と遅延制約を使い分ける
外部キー制約は既定では即時に検査されます。1文の終了時点で参照先がなければ、その文は失敗します。通常のAPI登録では、異常を早く返せる即時制約が扱いやすい選択です。
一方、大量取込や相互参照データでは、トランザクションの途中だけ一時的に参照が欠け、最後には整合することがあります。DEFERRABLE INITIALLY DEFERREDを指定した外部キーは、外側のトランザクションをCOMMITする時点まで検査を遅らせられます。
BEGIN;
INSERT INTO bom_lines (parent_part_id, child_part_id, quantity)
VALUES ('PART-A', 'PART-B', 2.0);
INSERT INTO parts (part_id, name, material_id)
VALUES ('PART-A', '組立品A', 'MAT-001');
INSERT INTO parts (part_id, name, material_id)
VALUES ('PART-B', '構成部品B', 'MAT-001');
COMMIT;この方式は順序の自由度を上げますが、違反を容認するものではありません。COMMIT時に参照切れが残れば、トランザクションは完了できません。エラー処理では全体をロールバックし、途中まで成功したように扱わないことが重要です。
接続内の全外部キーを一時的に遅延扱いにするPRAGMA defer_foreign_keys = ONもありますが、影響範囲が広いため、通常は必要な制約へDEFERRABLEを明記した方が契約を読み取りやすくなります。
子キーにも索引を用意する
SQLiteは親の削除や更新時に、該当する子レコードを検索してactionを判断します。子キーに索引がなければ、子表を先頭から走査する可能性があります。外部キー列の索引は必須ではありませんが、実務ではほとんどの場合に有効です。
単列ならparts(material_id)、複合外部キーなら同じ列順の複合索引を候補にします。部品表の主キー(parent_part_id, child_part_id)は親側からの検索には使えますが、child_part_idだけの逆引きには先頭列が合いません。そのため、例ではbom_lines(child_part_id)を追加しています。
索引は書き込みコストと保存容量も増やします。実際の検索条件、件数、EXPLAIN QUERY PLANの結果を見て、重複する索引を増やしすぎないようにします。
Pythonで有効化・再実行・異常系をまとめて検証する
次の例は、接続直後の有効化、親子の登録、冪等な再実行、孤児レコードの拒否、削除制限、最終検査をまとめた最小実装です。
import sqlite3
SCHEMA = """
CREATE TABLE materials (
material_id TEXT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE parts (
part_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
material_id TEXT NOT NULL,
FOREIGN KEY (material_id)
REFERENCES materials(material_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
CREATE INDEX parts_material_id_idx ON parts(material_id);
"""
def open_database() -> sqlite3.Connection:
conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys = ON")
enabled = conn.execute("PRAGMA foreign_keys").fetchone()[0]
if enabled != 1:
conn.close()
raise RuntimeError("foreign key enforcement is disabled")
return conn
def upsert_part(
conn: sqlite3.Connection,
part_id: str,
name: str,
material_id: str,
) -> None:
conn.execute(
"""
INSERT INTO parts (part_id, name, material_id)
VALUES (?, ?, ?)
ON CONFLICT(part_id) DO UPDATE SET
name = excluded.name,
material_id = excluded.material_id
""",
(part_id, name, material_id),
)
conn = open_database()
conn.executescript(SCHEMA)
with conn:
conn.execute(
"INSERT INTO materials (material_id, name) VALUES (?, ?)",
("MAT-001", "アルミニウム合金"),
)
upsert_part(conn, "PART-001", "支持板", "MAT-001")
upsert_part(conn, "PART-001", "支持板", "MAT-001")
try:
with conn:
upsert_part(conn, "PART-999", "参照切れ部品", "MAT-999")
except sqlite3.IntegrityError as exc:
print(f"rejected orphan record: {exc}")
try:
with conn:
conn.execute(
"DELETE FROM materials WHERE material_id = ?",
("MAT-001",),
)
except sqlite3.IntegrityError as exc:
print(f"rejected parent deletion: {exc}")
violations = conn.execute("PRAGMA foreign_key_check").fetchall()
if violations:
raise RuntimeError(f"foreign key violations: {violations}")
conn.close()値は?プレースホルダで渡し、SQL文字列へ直接連結しません。これは引用符の扱いを安定させ、SQLインジェクションを防ぐ基本です。表名や列名はプレースホルダにできないため、動的に切り替える必要がある場合は許可リストで限定します。
ON CONFLICT ... DO UPDATEにより、同じpart_idを再送しても重複行ではなく同じ状態へ収束します。ただし、冪等性を実現するにはIDの安定性も必要です。表示名から毎回IDを作るのではなく、送信元と受信先で共有できる不変IDを決めます。API連携では、イベントIDや更新版も別列で保持すると、古いデータによる巻き戻しを防ぎやすくなります。
バッチ取込ではステージングと検査結果を分ける
CSVや表計算から直接本番表へ流し込むと、何行目の何が悪かったのか追跡しにくくなります。まずステージング領域へ原文と行番号を保持し、次の順序で検証すると再処理しやすくなります。
- UTF-8など想定した文字コードで復号できるか
- 必須IDが空でなく、前後空白や全角・半角の変換規則が明示されているか
- 数量が数値で、単位が契約どおりか
- 真偽値や日時を送信元固有の表記から正規形へ変換できるか
- 親IDが同一バッチ内または既存マスタに存在するか
- 重複IDが同じ内容か、競合する内容か
検証済みデータは1トランザクションで親から子へ保存します。遅延制約を使う場合も、COMMIT後に成功を返します。失敗時は入力行番号、外部公開用のエラーコード、内部調査用の詳細を分離して記録します。CSVの内容やAPIトークン、個人情報を無条件にログへ出さないことも重要です。
既存DBの参照切れを監査する
外部キーを後から導入した場合や、一時的に検査を無効化した接続が疑われる場合は、次のPRAGMAで監査します。
PRAGMA foreign_key_check;違反があれば、子表名、行識別子、参照先の親表名、外部キー定義の番号が返ります。行識別子を持たない表では該当欄がNULLになる場合があります。定義の確認には次を使えます。
PRAGMA foreign_key_list('parts');PRAGMA integrity_checkだけでは外部キー違反を検出できません。ファイル構造などの整合性検査と、参照整合性検査は目的が異なるため、運用チェックには両方を組み込みます。
修復では、欠けた親を推測で作らないことが重要です。送信元へ確認し、親の復元、子の正しい付け替え、業務承認を伴う子の削除のいずれかを選びます。修復前後の件数と対象IDを監査ログに残します。
API・CAD・設計データへ再利用するポイント
外部キーはSQLiteだけの話ではなく、システム間のデータ契約を具体化します。APIでは親IDと子IDの意味をスキーマに記述し、存在しない参照には安定したエラーコードを返します。DB内部のエラー文をそのまま外部へ出すと構造や実装情報を漏らすため、公開応答と内部ログを分離します。
CADやBOM連携では、表示名、ファイル名、画層名を識別子にしない方が安全です。名称は変更され、同名も発生します。部品、版、図面、材料へそれぞれ不変IDを割り当て、対応表を保存します。単位系、版、座標系、状態も参照先マスタと組み合わせて検証すれば、単なるファイル交換から再利用可能な設計データ資産へ発展させられます。
外部へ配布するデータには公開可能なIDと属性だけを含め、原価、個人情報、認証情報は別の表・権限・出力経路へ分離します。外部キーで表が結べることと、利用者へ結合結果を公開してよいことは別問題です。
よくある失敗と設計上の対策
- 外部キーを書いただけで安心する：接続直後に
PRAGMA foreign_keys = ONを実行し、値が1か確認する。 - 任意項目のつもりが空文字になる：NULL、空文字、未設定コードの意味を契約で分ける。
- CASCADEを一律に使う：親子の所有関係と監査要件ごとにactionを決める。
- 子キーへ索引を作らない：親更新・削除時の検索を想定し、実行計画を確認する。
- 取込順序だけで解決しようとする：必要な関係だけ遅延制約にし、1トランザクションで確定する。
- 再実行で重複する：安定ID、一意制約、UPSERT、トランザクションを組み合わせる。
- アプリの事前確認だけに頼る：確認後の同時更新も考え、DB制約を最終防衛線にする。
まとめ
SQLiteの外部キー設計で重要なのは、REFERENCESを書くことだけではありません。接続ごとの有効化、親子キーの型とNULL、削除・更新action、複合キー、遅延制約、子キー索引、再実行、監査を一つの契約として設計する必要があります。
まずは接続初期化でPRAGMA foreign_keys = ONを保証し、必須参照にはNOT NULLを付け、削除時動作を業務の寿命に合わせます。バッチではトランザクションとforeign_key_checkを組み合わせ、エラーを入力行まで戻せるようにします。この土台があれば、SQLiteからAPI、DB、CAD、BOMへデータを受け渡しても、参照関係の意味を保ったまま再利用できます。
