物理FK vs 論理FK

物理FK vs 論理FK とは

物理外部キーと論理外部キーの違いをSQL操作を通して学びます。本レッスンでは、物理FK vs 論理FK の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。

なぜ重要か

ここまで学んだ FOREIGN KEY 制約は 物理FK と呼ばれ、DB レイヤで強制される本物の制約です。一方、現場の大規模システムや高スループットなサービスでは、テーブル間の関係を「コメントや命名規則だけ」で表現する 論理FK が採用されることもあります。どちらが正解という話ではなく、特性を理解したうえで使い分けるのがプロのデータベース設計者です。

物理FKと論理FKの違い

diagram (will load when visible)

物理FKはデータ整合性を絶対視する代わりに、書き込み性能・スキーマ柔軟性に犠牲を払います。論理FKは性能・柔軟性を取る代わりに、アプリ実装者が整合性を守る責任を負います。

物理FKを採用する典型ケース

  • 厳密な整合性が業務要件である (会計、医療、認証)
  • 小〜中規模のサービスで運用負荷を下げたい
  • ORM が FK を前提として動く (Rails / Django など)

SQL クエリ

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) );

論理FKを採用する典型ケース

  • 大量書き込みが発生するイベントログ・分析テーブル
  • シャーディングしたテーブルで物理FKが張れない
  • マイクロサービスで親テーブルが別 DB にある

論理FKは「明示的な制約は書かないが、customer_id という名前で関連を表現する」というスタイルです。テーブルの構造を見て JOIN するという慣習だけが頼りになります。

SQL クエリ

-- 論理FK の例 (制約はあえて張らない) CREATE TABLE event_log ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, -- users.id を指す (論理FK) event_type TEXT NOT NULL, occurred_at TIMESTAMP NOT NULL );

この場合、不正な user_id がイベントログに紛れ込んでも DB は何も言ってくれません。アプリ実装と運用ガイドで整合性を守ります。

比較表

プレーンテキスト

物理FK 論理FK 整合性 DB が保証 アプリと運用に依存 書き込み速度 やや遅い 速い スキーマ変更 ALTER が重い 自由 誤削除防止 できる できない 大量データ 不向き 向く ORM 連携 スムーズ 手動マッピング多め

両者の中間案

実務では両方の良いとこ取りを狙うパターンもあります。

  • マスター系テーブル (customers, products) は物理FK
  • イベントログ系テーブル (events, audits) は論理FK

このように同じ DB の中でテーブル特性ごとにポリシーを変えるのが現実解です。

物理FKは安全装置、論理FKは性能/柔軟性。テーブルの「役割」と「規模」を見て選ぶ。1 つの DB に両方が混在していても問題ない。

論理FK で運用するなら、定期的な「整合性チェックバッチ」を必ず持つこと。子レコードに孤児が無いかを SELECT で監視し、Slack 等に通知する仕組みが必須です。

マイクロサービスと論理FK

マイクロサービス構成では、各サービスが独自の DB を持つことが多く、サービス間で物理FKを張ること自体が不可能です。たとえば注文サービスから顧客サービスのテーブルを参照する場合、別 DB なので REFERENCES 句では繋げません。

SQL クエリ

-- 注文サービスの DB CREATE TABLE orders ( id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, -- 顧客サービスの users.id (論理FK) total INT NOT NULL );

この場合、整合性は イベント駆動アーキテクチャ で担保します。顧客サービスがユーザー削除イベントを発行し、注文サービスがそれを購読して必要な後処理を行う、というパターンです。物理FK と同等の整合性を別の手段で実現していると言えます。

物理FKを後付けする現場の知恵

初期のスタートアップではスピード重視で論理FKだけで作り、軌道に乗ってから物理FKに昇格させる、という戦略もよく取られます。手順は次のようになります。

SQL クエリ

-- 1) 既存データの整合性チェック SELECT e.id FROM event_log e LEFT JOIN users u ON u.id = e.user_id WHERE u.id IS NULL; -- 2) orphan を削除または修復 DELETE FROM event_log WHERE user_id NOT IN (SELECT id FROM users); -- 3) 物理FK を追加 ALTER TABLE event_log ADD CONSTRAINT fk_event_log_user FOREIGN KEY (user_id) REFERENCES users(id);

この手順を踏まないと FK 追加自体がエラーになります。先にデータをクレンジングしてから ALTER するのが鉄則です。

どちらを選ぶか迷ったら

小〜中規模のサービスを新規に作るなら、まずは物理FKを採用するのが安全側の選択です。論理FKを選ぶ理由 (大量書き込み、シャーディング、サービス分離) が無い限り、物理FKの安全性を捨てる必要はありません。

まとめ

  • 物理FKは DB が強制する本物の制約。論理FKは規約のみ
  • マスター系は物理FK、イベントログ系は論理FK が定石
  • 論理FKを選ぶなら整合性チェックの仕組みをセットで導入
  • どちらか一方ではなく、テーブルごとに使い分けるのが現実解
  • マイクロサービスでは物理FKは張れず、イベント駆動で整合性を担保する
  • 論理FK -> 物理FK 昇格時は先にデータをクレンジングする

次のレッスン

次は 外部キークイズ です。外部キー制約に関する理解度をクイズ形式でチェックし、知識を定着させます。

事前確認 — 進む前に次の 3 つができることを確認しましょう。

  1. 物理FK と 論理FK の要点を自分の言葉で説明できる
  2. このレッスンの最小コード (または操作手順) を見ずに書ける
  3. 練習問題やクイズで間違えた箇所を読み直して理解した

理解度チェック (30 秒)

Q. 物理FK と 論理FK とは何か、1 文で説明してください。

この章のポイント

A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。

関連レッスン

テーブル構造

schema.sql

CREATE TABLE users ( id INT PRIMARY KEY, name TEXT NOT NULL ); -- 論理FKのつもりだったが整合性チェックが甘く orphan が混入したログ CREATE TABLE event_log ( id INT PRIMARY KEY, user_id INT NOT NULL, event_type TEXT NOT NULL ); INSERT INTO users (id, name) VALUES (1, '田中'), (2, '佐藤'), (3, '鈴木'); INSERT INTO event_log (id, user_id, event_type) VALUES (1001, 1, 'login'), (1002, 2, 'login'), (1003, 1, 'purchase'), (1004, 99, 'login'), -- orphan (1005, 100, 'purchase'); -- orphan

期待される出力

iduser_idevent_type
100499login
1005100purchase

ヒント

query.sql
query.sql
学習モード
コードの実行結果
データベースを初期化中...