🔖 キーワード索引

この用語と一緒に検索・参照されやすいタグ。 関連ページに飛ぶときの手がかりにも使えます。

「foreign key」は統計データ分析の文脈で扱う重要概念のひとつ。 本ページでは「foreign key」を取り巻く中核キーワードを以下にチップで一覧化する。 各キーワードは関連する概念・手法・道具立てを含み、 文献検索や学習計画の起点になる。

これらのキーワードは「foreign key の理解 → 適用 → 検証」のプロセスを構成する。 各章で詳しく解説する。

🎨 直感で掴む

外部キー (foreign key, FK) とは、 ある表 (子表) の列が「別の表 (親表) の主キーに一致する値しか取れない」というデータベース制約のことです。 「存在しない参照を禁止する」というたった 1 つの規則で、 表をまたいだデータの矛盾 (孤児レコード・参照切れ) を防ぎます。

• 図書館の貸出記録: 貸出表の `book_id` は本表 `books.id` に存在する値だけ。 「存在しない本を貸した」というレコードは INSERT 時に DB が拒否 (`ERROR: insert violates foreign key constraint`)。 これがアプリ側コードでなく DB エンジンで強制される点が肝心。
• SSDSE-B-2026 の都道府県 ID も同じ原理。 県別人口表の `pref_code` は都道府県マスタ `prefectures.code` の 47 値のどれかに必ず一致。 例えばタイポで `R48000` を入れても DB が弾く → CSV からのバルクロード時に即座に欠陥が露見する。
• ON DELETE / ON UPDATE 動作: 親表のレコードを消したとき子表をどうするかを宣言できる。 CASCADE (連鎖削除) / RESTRICT (親に子があれば親削除を拒否) / SET NULL (子の FK 列を NULL に) / NO ACTION の 4 種類。 顧客と注文なら通常 RESTRICT、 タグと付与履歴なら CASCADE が定石。
• 複合 FK と循環参照: FK は複数列にもできる (例: `(team_id, season)` で過去のチーム編成を参照)。 ただし表 A → B → A という循環 FK は、 初期データ投入が困難になるため避けるか、 一方を NULL 許容にする設計が必要。

📐 定義・数式

直感の次は、 厳密な定義を確認します。 数式は言語の一種で、 一度書き慣れれば「言葉より速く伝えられる」便利な道具。 慣れていない方は、 各記号が何を表すかを「🔬 数式を言葉で読み解く」で 1 つずつ確認してください。

📐 形式的定義: 参照整合性 (Referential Integrity)

テーブル $R$ の列集合 $F$ が、 テーブル $S$ の主キー $K$ を参照する外部キーであるとは、 次が常に成り立つことを言う:

$$ \forall t \in R: \quad t[F] = \mathrm{NULL} \;\;\lor\;\; \exists s \in S \text{ s.t. } s[K] = t[F] $$

つまり 「参照先が存在するか、 さもなくば明示的に NULL」の二択しか許さない。 これを「参照整合性」と呼びます。

🔬 数式を言葉で読み解く

💡 「数式を言葉で読み解く」 ことで分かる重要点: FK は「等号 (=) 」ではなく「存在量化子 ∃」で定義される — 参照先がたった 1 行でもあれば OK で、 重複参照は許されるということ。

📐 外部キー制約の 5 形式 (ON DELETE / ON UPDATE) を SSDSE-B-2026 都道府県表で完全整理

外部キーは「参照する」だけの仕組みではなく、 親レコードが消えた/書き換わった時の子側の挙動を 5 種類から選ぶのが本質である。 SSDSE-B-2026 の親表 pref(Code PK, Prefecture) と子表 pop(Code FK→pref, Year, Population) を例に、 5 形式を比較しよう。 47 都道府県 × 12 年 = 564 行の小さな例でも、 ON DELETE の選び方で「東京都の行を 1 つ消す」だけで残る人口データの形が全く違う。

このコードでやること: SSDSE-B-2026 の都道府県マスタと人口データを sqlite に投入し、 5 種類の ON DELETE 形式を順に試して挙動の差を可視化する。

📥 入力データ (SSDSE-B-2026 抜粋、 pref テーブル):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import sqlite3, pandas as pd
df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
tokyo = df[df['Code']=='R13000'][['Code','SSDSE-B-2026','A1101']]  # 東京 12 年分(2012-2023)
con = sqlite3.connect(':memory:')
con.execute("PRAGMA foreign_keys=ON")
con.execute("CREATE TABLE pref(Code TEXT PRIMARY KEY, Prefecture TEXT)")
con.execute("CREATE TABLE pop(Code TEXT, Year INT, Pop INT, "
            "FOREIGN KEY(Code) REFERENCES pref(Code) ON DELETE CASCADE)")
con.execute("INSERT INTO pref VALUES('R13000','東京都')")
con.executemany("INSERT INTO pop VALUES(?,?,?)", tokyo.values.tolist())
print('削除前 pop 件数:', con.execute('SELECT COUNT(*) FROM pop').fetchone()[0])
con.execute("DELETE FROM pref WHERE Code='R13000'")
print('削除後 pop 件数 (CASCADE 動作):', con.execute('SELECT COUNT(*) FROM pop').fetchone()[0])

📤 実行結果:

💬 結果の読み方: 親 1 行を消しただけで子 12 行が連鎖削除された。 SSDSE-B-2026 では取り返しがつくが、 本番の人口統計 DB では 12 年分（2012-2023）の貴重な記録を一瞬で失う。 マスタ表の FK には NO ACTION か RESTRICT がデフォルト推奨、 CASCADE は「親が消えたら子も無意味」な場合限定の例外運用とする。

🔬 数式を言葉で読み解く — 数式を「言葉」に翻訳

数式を眺めるだけでは身につかないので、 各記号がどんな役割を担っているかを言葉で押さえます。 「数式を音読する習慣」がつくと、 論文や教科書を読むスピードが体感で 2 倍ほど上がります。

親テーブル

参照される側（例: customers）

子テーブル

参照する側（例: orders）

PK

主キー（参照先）

FK

外部キー（参照元）

CASCADE

親の削除に連鎖して子も削除

🔬 ここから: 外部キーを SSDSE-B-2026 で徹底解剖

外部キー (Foreign Key, FK) は「列の値が、 別テーブルの主キーに存在する」ことをデータベース自身に保証させる制約です。 一見地味ですが、 ここを怠ると分析結果はあっという間に「幻のレコード」（参照先のない孤児行）に汚染されます。 以下では data/raw/SSDSE-B-2026.csv （564 行 × 112 列、 47 都道府県 × 12 年）を 2 つの正規化テーブル に分解しながら、 ① 数式での定義、 ② sqlite3 と pandas での実装、 ③ CASCADE / SET NULL / RESTRICT / NO ACTION の挙動差、 ④ 落とし穴 (循環参照・遅延制約・NULL の例外) を段階的に確認します。

🔬 ON DELETE 句の選択を「数式を言葉で読み解く」: 親消滅時に子はどう振る舞うか

外部キー F が親キー K を参照しているとき、 親レコード s ∈ S を削除すると子側 t ∈ R: t[F]=s[K] の運命は ON DELETE 句で決まる。 4 通りを集合論で固定する。

$$ \mathrm{children}(s) = \{ t \in R \mid t[F] = s[K] \},\quad \delta_s : \mathrm{children}(s) \to \{\text{remove, NULL, default, deny}\} $$

🔬 外部キーが「効かない」3 大事故と SSDSE-B-2026 で再現する検証コード

外部キー制約は「貼ってあるから安心」とは限らない。 実務でよく遭遇する 「FK が宣言されているのに整合性が崩れる」3 大事故を、 SSDSE-B-2026 sqlite で再現して原因を可視化する。 単なる教科書知識ではなく、 自分の手元で壊して直すまでが理解の単位。

このコードでやること: 事故 A (PRAGMA 忘れ) を SSDSE-B-2026 で再現し、 FK 制約が「宣言だけで機能しない」状態を可視化する。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

import sqlite3
con = sqlite3.connect(':memory:')
# わざと PRAGMA foreign_keys=ON を忘れる
con.execute("CREATE TABLE pref(Code TEXT PRIMARY KEY, Prefecture TEXT)")
con.execute("CREATE TABLE pop(Code TEXT, Year INT, Pop INT, FOREIGN KEY(Code) REFERENCES pref(Code))")
con.execute("INSERT INTO pref VALUES('R13000','東京都')")
# 親に存在しないコードを子に INSERT (PRAGMA OFF なので通ってしまう)
try:
    con.execute("INSERT INTO pop VALUES('R99999', 2020, 123456)")
    print('FK 違反なのに INSERT 成功 → 制約が機能していない')
except sqlite3.IntegrityError as e:
    print('正常に FK 違反検出:', e)
# 修正: PRAGMA を ON
con.execute("PRAGMA foreign_keys=ON")
try:
    con.execute("INSERT INTO pop VALUES('R88888', 2020, 99999)")
    print('PRAGMA ON 後も INSERT 成功 → 想定外')
except sqlite3.IntegrityError as e:
    print('PRAGMA ON 後は正しく拒否:', e)

📤 実行結果:

💬 結果の読み方: sqlite では PRAGMA foreign_keys=ON を実行するまで FK 制約は「装飾」に過ぎず、 不整合データが平然と入る。 これが本番事故の典型例 (「FK 貼ったはずなのにゴミデータが…」の正体)。 PostgreSQL/MySQL/SQL Server はデフォルトで FK が有効なので、 sqlite からの移行時に「以前は通っていた不正データが拒否される」という新たな課題が顕在化する。

運用上の対策セット: (1) アプリケーション起動時に必ず PRAGMA foreign_keys=ON。 (2) ORM (SQLAlchemy/Django) なら event.listens_for(Engine, "connect") でフックを設定。 (3) 定期的に PRAGMA foreign_key_check を実行して孤児を検出。 (4) CI/CD で「FK 制約一覧を比較するスナップショットテスト」を実装し、 知らぬ間に制約が消える事故を防ぐ。 (5) 本番に近づくほど sqlite を捨て PostgreSQL/MySQL に移行する。

🧮 実値で計算してみる

数式だけでは「実感」が湧きにくいので、 具体的な数値で 1 度手計算してみると理解が定着します。 以下の例は、 本サイトで扱う SSDSE-B-2026 や公開教材に近い形式で用意しました。

典型的な ER 関係：

手計算で得た値と、 後述の Python 実装で算出した値が一致することを確認すると、 「数式とコードの対応関係」がクリアに見えるようになります。

🧮 SSDSE-B-2026 を 2 つの正規化テーブルに分解する

SSDSE-B-2026 の生 CSV は「年 × 都道府県 × 112 列」のフラット形式です。 これを次の 2 表に正規化 すれば、 都道府県名やローマ字読みなどの静的属性を 1 箇所にまとめられ、 更新コストと不整合リスクが激減します。

2023 年 5 行（実値）を見ると、 Code 列がそれぞれ pref の 1 行を指していることが分かります:

🧮 SSDSE-B-2026 で「孤児レコード」を発見する 3 つの SQL パターンと修復手順

外部キー制約が「後から」付けられた DB では、 制約導入時点ですでに親に存在しない値が子に残っている 孤児レコード (orphan record) が大量に紛れていることが多い。 SSDSE-B-2026 のような行政データでも、 市町村合併で消えた旧コードや、 集計年度の都合で揃わない年が孤児になる。 ここでは孤児発見・分類・修復の 定型 SQL を 3 種類提示する。

このコードでやること: SSDSE-B-2026 pop 表に「存在しない都道府県コード R99999」が紛れている想定で孤児を検出し、 修復方針 (削除 / NULL 化 / 新規マスタ追加) を選択する。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import sqlite3, pandas as pd
con = sqlite3.connect(':memory:')
con.execute("CREATE TABLE pref(Code TEXT PRIMARY KEY, Prefecture TEXT)")
con.execute("CREATE TABLE pop(Code TEXT, Year INT, Pop INT)")
con.executemany("INSERT INTO pref VALUES(?,?)", [('R01000','北海道'),('R13000','東京都'),('R27000','大阪府')])
# 孤児を仕込む: R99999 は pref に存在しない
con.executemany("INSERT INTO pop VALUES(?,?,?)", [('R01000',2020,5224614),('R13000',2020,14047594),('R99999',2020,123456)])
orphans = pd.read_sql("SELECT pop.* FROM pop LEFT JOIN pref ON pop.Code=pref.Code WHERE pref.Code IS NULL", con)
print('孤児レコード:')
print(orphans)
# 修復方針 A: 削除
con.execute("DELETE FROM pop WHERE Code NOT IN (SELECT Code FROM pref)")
print('修復後 pop 件数:', con.execute('SELECT COUNT(*) FROM pop').fetchone()[0])

📤 実行結果:

💬 結果の読み方: LEFT JOIN ... IS NULL で R99999 を 1 件特定し、 削除で 3 → 2 件に整合化。 実務では「削除」は最終手段で、 まず その孤児が何を意味するか (新コード化された旧県? データ入力ミス? 集計対象外?) を業務側に確認し、 修復方針を選ぶ。 確認なしに削除すると、 後で「あの値はどこ?」と問われて取り返しがつかない。

3 値論理の落とし穴: 上記表の NOT IN パターンは、 親 pref.Code に NULL が 1 つでも含まれると 結果が 0 件になる。 これは SQL の 3 値論理 (TRUE/FALSE/NULL) で x NOT IN (..., NULL, ...) が常に UNKNOWN を返すため。 SSDSE-B-2026 のマスタは NULL を含まないが、 業務 DB では NOT EXISTS 形式を必ず使うのが安全。

📝 理解度チェック (12 問): 外部キー設計・運用の判断力を試す

外部キーは「制約を貼ればよい」ではなく、 どの場面でどの形式を選び、 どう運用するかの判断連鎖が本質である。 SSDSE-B-2026 を題材に 12 問で総点検しよう。 各問に「正答 + 根拠」を併記、 自分の答えとずれた箇所が学習ポイント。

採点目安: 10/12 以上で実務即戦力。 7-9 で基礎は合格、 NO ACTION/RESTRICT/CASCADE の使い分けと DEFERRED の用途を復習。 6 以下なら本ページ冒頭の「📐 定義・数式」と「⚠️ 外部キー列に INDEX を貼り忘れる」節を再読し、 sqlite で実際に 5 形式を試して挙動を体得すること。

関連用語: 主キー・候補キー・参照整合性・正規化・トランザクション・ACID・インデックス・複合キー・サロゲートキー・DWH・dbt・データウェアハウス・ETL・ELT・SSDSE。

🧮 数式に値を入れて手で計算する: 参照整合性違反件数

合成データで親 (顧客) に存在しない FK を持つ子 (注文) の数を計算する。

Step 1: 親と子の ID 集合

Step 2: 違反検出

🐍 Python で再現

1
2
3
4
5

parent = {1, 2, 3, 4, 5}
child_fks = [1, 2, 3, 6, 7, 1, 2, 8]
invalid = [fk for fk in child_fks if fk not in parent]
print(f"違反 FK: {set(invalid)}")
print(f"件数: {len(invalid)}")

📤 実行結果

💬 手計算 (Step 2) 3 件と Python 出力が完全一致。

🐍 Python 実装

公的統計（SSDSE-B-2026）を題材に、 最小限の Python コードで動作させます。 ファイルパス（data/raw/SSDSE-B-2026.csv）は自分の環境に合わせて変更してください。 まずはこのまま動かすことが理解の最短ルートです。

1
2
3
4
5
6
7
8
9

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON')
conn.execute('CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT)')
conn.execute('CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id))')
conn.execute('INSERT INTO customers VALUES (1, "Alice")')
conn.execute('INSERT INTO orders VALUES (100, 1)')   # OK
# conn.execute('INSERT INTO orders VALUES (101, 999)')  # 例外

▶ 実行 を押せばこのページの中でそのまま動きます（ライブラリもデータも同梱済みで、 準備は要りません）。 手元の Python に移して動かすときは pip install pandas が必要です。 読んでいるデータは data/raw/SSDSE-B-2026.csv。 日本語を含むので encoding='cp932' の指定を落とさないでください。

本サイトの全コードは 論文一覧ページ から実例として確認できます。 自分のデータで試したい場合は、 列名・欠損記号・単位の違いだけ調整すれば、 ほぼそのまま流用できます。

👣 ステップバイステップ実例

「外部キー」を初めて使う方向けに、 ハンズオン的な実行手順を整理します。 上の Python 実装と組み合わせて、 1 度自分の手でなぞってみることを強く推奨します。

1. 環境準備：このページのコードは ▶ 実行 ボタンでそのまま動くので、 まずは何も入れずに試す。 手元で動かしたくなったら Python 3.9 以上に pandas・scipy・matplotlib を入れ、 Jupyter Notebook か Google Colab を使うと試行錯誤しやすい。
2. データ取得：本サイト題材の SSDSE-B-2026 を data/raw/ に配置（または自分のデータを用意）。 列名と単位を確認。
3. 探索的に観察：df.head()、 df.describe()、 df.isna().sum() で全体像を把握。 ここで欠損や外れ値の見当を付ける。
4. 前提検証：外部キー をこのデータに当てはめてよいか（このページの「⚠️ よくある落とし穴」で挙げた FK チェックがオフ・循環参照 など）を確認。 NG なら別手法を検討。
5. 本処理：上のコードブロックを参考に、 関数を呼び出して値を取得。 中間出力をその都度プリントして合っているか確認。
6. 結果可視化：散布図、 棒グラフ、 ヒートマップなど、 解釈しやすい図を 1〜2 枚作る。 タイトルには結論を書く。
7. 解釈・記録：「📝 レポートでの報告」の 5 点セットに沿って Notebook に書き残す。 後の自分のために結論・限界・次の一手を明記。
8. 共有：Notebook を GitHub や Drive に置き、 関係者にレビュー依頼。 ピアレビューで穴が見つかることが多いので大事。

この 8 ステップを 1 度回すと、 「用語を読んで分かった気になる」段階から「実際に使える」段階に進めます。 知識は身体で覚えるのが結局のところ最速です。

🐍 Python 実装 ①: sqlite3 で FK 制約を作って違反を検出

🎯 このコードでやること: SSDSE-B-2026 から都道府県マスタと年別人口表を作り、 sqlite3 で FOREIGN KEY 制約を有効化。 存在しない都道府県コード (R99999) を挿入して FK 違反を意図的に発生させ、 DB が拒否することを確認します。

📥 入力データ (SSDSE-B-2026 抜粋、 2023 年 47 県のうち先頭 5 行):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

import sqlite3
import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
d23 = df[df['SSDSE-B-2026']==2023][['Code','Prefecture','A1101']]

con = sqlite3.connect(':memory:')
con.execute("PRAGMA foreign_keys = ON")  # sqlite はデフォルト OFF なので必須

con.execute("CREATE TABLE pref (code TEXT PRIMARY KEY, name TEXT)")
con.execute("CREATE TABLE pop (code TEXT, year INT, val INT, "
            "FOREIGN KEY(code) REFERENCES pref(code))")

con.executemany("INSERT INTO pref VALUES (?,?)",
                d23[['Code','Prefecture']].values.tolist())
con.execute("INSERT INTO pop VALUES ('R01000', 2023, 5092000)")

try:
    con.execute("INSERT INTO pop VALUES ('R99999', 2023, 9999)")
except sqlite3.IntegrityError as e:
    print("FK 違反検出:", e)

📤 実行すると次の出力が得られる:

💬 結果の読み方: R99999 は pref テーブルに存在しないコードなので、 DB が即座にエラーを返した。 もし PRAGMA を忘れて FK が無効だったら、 この孤児行はそのまま挿入され、 後の集計で「正体不明の県」として混入する。 sqlite では 毎接続ごとに PRAGMA foreign_keys=ON を発行する のが鉄則。

🐍 Python 実装 ②: CASCADE / SET NULL / RESTRICT の挙動を 1 つのスクリプトで比較

🎯 このコードでやること: 親テーブル (pref) の行を削除したときに、 子テーブル (pop) がどう振る舞うかを 3 つの ON DELETE オプションで実演する。 CASCADE は連鎖削除、 SET NULL は孤児化を明示的に許容、 RESTRICT は削除を拒否する。

📥 入力データ: SSDSE-B-2026 の 2023 年 5 県を pref に、 各県の人口を pop に投入した直後の状態:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

import sqlite3

def setup(on_delete):
    c = sqlite3.connect(':memory:')
    c.execute("PRAGMA foreign_keys=ON")
    c.execute("CREATE TABLE pref (code TEXT PRIMARY KEY, name TEXT)")
    c.execute(f"CREATE TABLE pop (code TEXT, year INT, val INT, "
              f"FOREIGN KEY(code) REFERENCES pref(code) ON DELETE {on_delete})")
    c.execute("INSERT INTO pref VALUES ('R01000','北海道')")
    c.execute("INSERT INTO pop VALUES ('R01000', 2023, 5092000)")
    return c

for opt in ["CASCADE", "SET NULL", "RESTRICT"]:
    c = setup(opt)
    try:
        c.execute("DELETE FROM pref WHERE code='R01000'")
        rows = c.execute("SELECT * FROM pop").fetchall()
        print(f"[{opt:8}] 削除成功 → pop 残: {rows}")
    except sqlite3.IntegrityError as e:
        print(f"[{opt:8}] 削除拒否 → {e}")

📤 実行すると次の出力が得られる:

💬 結果の読み方: CASCADE は北海道の人口行も道連れに消える (履歴データが欲しい場合は危険)。 SET NULL は人口行は残るが code=None になり、 集計時に「県不明」として現れる。 RESTRICT は 子に参照されている親は消せない という最も保守的な動作。 監査ログを残したいなら RESTRICT、 マスタ更新が頻繁なら SET NULL + 履歴テーブル併用 が定石。

🐍 Python 実装 ③: pandas で外部キー結合 + 整合性チェック

🎯 このコードでやること: SSDSE-B-2026 の都道府県コードを「地方区分マスタ」と結合し、 indicator=True で結合結果を 3 値 (both / left_only / right_only) で集計。 FK 違反 (孤児行) を pandas 側で能動的に検出する方法。

📥 入力データ: 地方区分マスタ（5 行のみ。 47 県中 5 県しか登録していないので、 残り 42 県は left_only として可視化されるはず）:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

import pandas as pd

df     = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
d23    = df[df['SSDSE-B-2026']==2023][['Code','Prefecture','A1101']]
region = pd.DataFrame({
    'Code'  : ['R01000','R02000','R13000','R27000','R47000'],
    'Region': ['北海道','東北','関東','近畿','九州沖縄']
})

m = d23.merge(region, on='Code', how='left', indicator=True)
print("_merge 集計 :", m['_merge'].value_counts().to_dict())
print("孤児行 (region 欠落) =", int((m['_merge']=='left_only').sum()))

📤 実行すると次の出力が得られる:

💬 結果の読み方: 47 県のうち 5 県だけがマスタに含まれているので、 残り 42 県は left_only。 もし FK 制約が DB 側で有効ならそもそも 42 県は登録できなかった。 pandas は制約を持たないので、 結合後に indicator 列で能動チェックするしかない — これが「分析用データレイクではいつ FK が壊れたか分からない」典型シナリオ。

🐍 Python 実装 ④: pandas.merge の validate で多重度を検証

🎯 このコードでやること: pandas 1.0+ の merge(validate=...) を使い、 FK の多重度 (one-to-one / one-to-many / many-to-one / many-to-many) を実行時にアサート。 想定外の重複を即座に MergeError として失敗させる。

📥 入力データ: SSDSE-B-2026 全 564 行 (47 県 × 12 年)。 Code 列は親側 pref (47 行) に対して N:1 の関係:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

import pandas as pd

df   = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
pop  = df[['SSDSE-B-2026','Code','A1101']].rename(columns={'SSDSE-B-2026':'year'})
pref = df[['Code','Prefecture']].drop_duplicates()

# 正しい多重度 (many_to_one): pop 側が多、 pref 側が一
ok = pop.merge(pref, on='Code', validate='many_to_one')
print("OK 結合: ", ok.shape, "unique Code:", ok['Code'].nunique())

# 誤った多重度 (one_to_one) を指定 → MergeError
try:
    pop.merge(pref, on='Code', validate='one_to_one')
except Exception as e:
    print("NG 結合:", type(e).__name__, "-", e)

📤 実行すると次の出力が得られる:

💬 結果の読み方: validate 引数は実質的に「想定 FK 多重度の事前アサーション」として機能する。 ETL ジョブの境界で validate='many_to_one' を入れておけば、 マスタ側に重複が混入した瞬間にバッチが落ちる — 「気づかぬまま結合結果が膨らんでいた」事故を未然に防ぐ。

📋 ON DELETE / ON UPDATE オプション早見表

⚠️ RESTRICT と NO ACTION は 名前は違うが SQL 標準では「いつチェックするか」だけが違う。 RESTRICT は即時、 NO ACTION は文末 (deferrable なら commit 時)。 PostgreSQL では明確に区別、 MySQL/InnoDB では実質同じ挙動。 移植性を重視するなら RESTRICT 一択にしておくと事故が減る。

🐍 Python 実装 ⑤: 47×12=564 行を実テーブルにロードして JOIN 性能を測る

🎯 このコードでやること: SSDSE-B-2026 の全 564 行を sqlite に投入し、 FK 列にインデックスがある場合と無い場合で JOIN 実行時間を比較する。 FK 列のインデックスが性能に直結する ことを実測で示す。

📥 入力データ: SSDSE-B-2026 全行 (年 × 都道府県のクロス):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

import sqlite3, time
import pandas as pd

df   = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
pop  = df[['SSDSE-B-2026','Code','A1101']].rename(columns={'SSDSE-B-2026':'year'})
pref = df[['Code','Prefecture']].drop_duplicates()

def benchmark(with_index):
    c = sqlite3.connect(':memory:')
    c.execute("PRAGMA foreign_keys=ON")
    c.execute("CREATE TABLE pref (code TEXT PRIMARY KEY, name TEXT)")
    c.execute("CREATE TABLE pop (code TEXT, year INT, val INT, "
              "FOREIGN KEY(code) REFERENCES pref(code))")
    c.executemany("INSERT INTO pref VALUES (?,?)", pref.values.tolist())
    c.executemany("INSERT INTO pop VALUES (?,?,?)",
                  pop[['Code','year','A1101']].values.tolist())
    if with_index:
        c.execute("CREATE INDEX idx_pop_code ON pop(code)")
    t0 = time.perf_counter()
    for _ in range(500):
        c.execute("SELECT p.name, SUM(o.val) FROM pop o JOIN pref p "
                  "ON o.code=p.code GROUP BY p.name").fetchall()
    return time.perf_counter() - t0

no_idx = benchmark(False)
w_idx  = benchmark(True)
print(f"index なし: {no_idx:.3f}s / index あり: {w_idx:.3f}s / 高速化率: {no_idx/w_idx:.2f}x")

📤 実行すると次のような出力が得られる (環境依存。 564 行スケールなので倍率は控えめ):

🕐 この 3 つの数値は実行のたびに変わります（マシンや同時に動いているプロセスに左右されます）。
💬 結果の読み方: 564 行では INDEX を張っても速くなりません（高速化率はほぼ 1.0 倍）。 これは失敗ではなく当然の結果で、 564 行なら SQLite は全走査しても一瞬で終わるため、 INDEX を引く手間のほうが相対的に大きくなるからです。 「INDEX を張ったのに速くならない」は小さい表では正常と覚えてください。 差が出るのは行数が増えてからで、 1,000 万行スケールでは数百倍の差に化けることが知られています。 なお「FK を張れば自動で速くなる」と勘違いされがちですが、 PostgreSQL も MySQL も FK 列の INDEX は自動では作りません。 設計時に明示的に CREATE INDEX を入れる必要があります。

🧩 複合外部キー (Composite FK) と SSDSE 時系列

SSDSE-B-2026 では「年 × 都道府県」の組が一意。 主キーを (year, Code) という複合主キーにすると、 関連する「年次イベント表」からは (year, Code) を 1 つの複合外部キーとして参照することになります。

$$ \mathrm{event}(year, code) \xrightarrow{\;FK\;} \mathrm{pop}(year, code) $$

この設計だと、 「2023 年の北海道で起きたイベント」を引いた瞬間に pop.A1101 (その年の人口) を 1 回の JOIN で取り出せる。 時系列マスタを複合 PK にする のは、 パネルデータ分析で頻出の正規化テクニックです。

複合 FK の落とし穴は 「片方だけ NULL」 の扱い。 SQL 標準では MATCH SIMPLE (デフォルト) ならどれか 1 つでも NULL なら制約をスキップ、 MATCH FULL なら全部 NULL か全部非 NULL のどちらかでなければエラー、 MATCH PARTIAL なら部分一致を許す (実装稀)。 PostgreSQL は 3 種すべて、 MySQL は MATCH 句自体が無視される (常に SIMPLE 相当)。

🐍 Python 実装 ⑥: 4 表構成を 1 スクリプトで構築・FK 連結確認

🎯 このコードでやること: SSDSE-B-2026 から 4 表 (pref / year / pop / econ) を作成し、 すべての FK 制約をかけた状態で 4 表 JOIN を実行。 1 つでも FK が壊れていれば結合結果の件数が変化することを確認する。

📥 入力データ (SSDSE-B-2026 をピボットして抜粋。 A1101=総人口、 L3221=消費支出):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

import sqlite3
import pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1]).rename(columns={'SSDSE-B-2026':'year'})

c = sqlite3.connect(':memory:')
c.execute("PRAGMA foreign_keys=ON")
c.execute("CREATE TABLE pref (Code TEXT PRIMARY KEY, Prefecture TEXT)")
c.execute("CREATE TABLE yr   (year INT  PRIMARY KEY)")
c.execute("""
  CREATE TABLE pop (year INT, Code TEXT, A1101 INT,
    PRIMARY KEY(year, Code),
    FOREIGN KEY(Code) REFERENCES pref(Code),
    FOREIGN KEY(year) REFERENCES yr(year))""")
c.execute("""
  CREATE TABLE econ (year INT, Code TEXT, val INT,
    FOREIGN KEY(year, Code) REFERENCES pop(year, Code))""")

c.executemany("INSERT INTO pref VALUES (?,?)",
              df[['Code','Prefecture']].drop_duplicates().values.tolist())
c.executemany("INSERT INTO yr   VALUES (?)",
              [(y,) for y in df['year'].drop_duplicates()])
c.executemany("INSERT INTO pop  VALUES (?,?,?)",
              df[['year','Code','A1101']].values.tolist())
c.executemany("INSERT INTO econ VALUES (?,?,?)",
              df[['year','Code','L3221']].fillna(0).values.tolist())

n = c.execute("""SELECT COUNT(*) FROM econ e
                       JOIN pop  p ON e.year=p.year AND e.Code=p.Code
                       JOIN pref r ON p.Code=r.Code
                       JOIN yr   y ON p.year=y.year""").fetchone()[0]
print("4 表 JOIN 件数:", n, "(期待 564)")

📤 実行すると次の出力が得られる:

💬 結果の読み方: 4 表すべて FK が成立しているので、 期待通り 564 行が JOIN 結果に出る。 もし途中で 1 件でも孤児行があれば INSERT 段階で IntegrityError が出て止まる。 つまり FK 制約は「データの形が正しいことの 24 時間連続テスト」 として機能する。

🕰 外部キーの歴史と現在地

外部キーの概念は 1970 年代の Codd の関係モデル論文に既に登場していますが、 SQL の標準仕様として FOREIGN KEY ... REFERENCES 構文が定着したのは SQL-89/92。 主要 RDBMS の対応時期を整理すると:

⚠️ 「クラウド DWH では FK 宣言が effectively NO-OP」 である点は実務で何度も事故を生んでいる。 SSDSE-B-2026 を BigQuery にロードして「FK 張ってるから安心」と思っていると、 後段 ETL で孤児行が混入する。 必ず dbt test の relationships テストや Great Expectations の expect_column_values_to_be_in_set を併用する。

🗑 大量削除戦略: CASCADE 爆発を避けるバッチ削除

SSDSE-B-2026 のような 564 行スケール であれば ON DELETE CASCADE でも瞬時に処理が終わりますが、 実務では「親 1 行に対して子が 1 億行」という規模も珍しくありません。 そうしたケースでは DELETE FROM pref WHERE code='R13000' 1 文だけで 子側 1 億行が単一トランザクションで消える ため、 ログが膨張して DB が停止寸前に追い込まれます。

正攻法は次の 3 段階で 子側をバッチで先に削除 してから親を消すこと:

1. BEGIN; DELETE FROM pop WHERE code='R13000' LIMIT 10000; COMMIT; をループで実行
2. 子側の対象行が 0 になったことを SELECT COUNT(*) で確認
3. 最後に親 DELETE FROM pref WHERE code='R13000' を実行 (CASCADE でなく RESTRICT 設計でも通る)

💡 ループ間に sleep(0.1) を入れると、 他トランザクションへの影響を最小化できる。 「削除はバッチ、 挿入は CASCADE 任せ」が現代的な使い分け。

📊 SSDSE-B-2026 で実証した FK 効果の総まとめ

この表を一言でまとめると: 「FK は DB 側でデータの形を 24 時間チェックし続けるテストコード」。 設計時の数十秒を惜しんで FK を省くと、 後で「いつ・どこで壊れたか分からない」孤児データに数日間振り回されるのが典型的な事故パターン。

❓ よくある疑問 (Deep FAQ)

Q1. FK を張ると性能が劣化する、 と聞いた。 ベンチマーク的に本当か？

A. 一般に INSERT/DELETE は数 % 〜十数 % 遅くなる。 SELECT には基本的に影響しない (FK は宣言であって実行時チェックは挿入/削除時のみ)。 ただし子テーブルが大きいときの DELETE は CASCADE の連鎖で爆発的に重くなることがあるため、 「FK そのもの」ではなく「CASCADE 設計」のコスト。 上述の SSDSE-B-2026 ベンチで見た通り、 INDEX を併用すれば SELECT は 1.5 倍速くなる方向にすら倒れる。

Q2. アプリ側でチェックすれば FK は不要では？

A. 短期的にはそう見えるが、 アプリは複数存在し、 バッチ・管理画面・直接 SQL の 3 経路から書き込みが入る。 FK は「すべての経路から守る最後の砦」。 「うちは API 経由しか書き込まない」と言っていた現場で、 障害復旧の手動 UPDATE が孤児行を作った事例は枚挙にいとまがない。

Q3. NoSQL ではどうする？

A. MongoDB / DynamoDB / Cassandra など主要 NoSQL は原則 FK 概念を持たない。 代わりに「非正規化して埋め込む」(子データを親ドキュメント内に丸ごと格納) か、 アプリ層で整合性をループチェックする。 Neo4j など グラフ DB は逆に「関係そのものがファーストクラス」なので、 FK 以上に強力なリンク整合性を持つ。

Q4. CASCADE と SET NULL のどちらを既定にすべき？

A. 業務ドメイン次第。 注文ヘッダと注文明細のような「親なくして子は意味を成さない」関係は CASCADE。 ユーザと過去レビューのような「ユーザは消えてもレビュー履歴は残したい」関係は SET NULL + 「退会済ユーザ」表示。 SSDSE のようなマスタ表 (都道府県) は RESTRICT にして、 そもそも消せないように守るのが鉄則。

Q5. 自己参照 FK (社員 → 上司の社員 ID) はどう設計する？

A. 上司 ID 列を FOREIGN KEY (manager_id) REFERENCES employee(id) と同じ表に張る。 NULL を許容することで「役職トップ (上司なし)」を表現する。 PostgreSQL なら WITH RECURSIVE で組織ツリーを 1 クエリで遡れる。

Q6. データ移行で一時的に FK を外したい

A. PostgreSQL なら ALTER TABLE ... DISABLE TRIGGER ALL や SET CONSTRAINTS ALL DEFERRED、 MySQL なら SET foreign_key_checks = 0。 ただし移行後に必ず VALIDATE CONSTRAINT で整合性を再確認すること。 外したまま再有効化を忘れると、 数か月後に「外せていた頃に入った孤児行」が問題化する。

📌 1 ページチートシート (DDL テンプレート集)

普段の DDL でそのまま使えるテンプレート。 SSDSE-B-2026 を想定した命名:

💡 テンプレート選択の指針: マスタ (都道府県・通貨・国コード) は RESTRICT、 ライフサイクル同一 (注文ヘッダ/明細) は CASCADE、 ユーザ系で履歴を残したいなら SET NULL/DEFAULT。 これだけ覚えれば 90% の設計判断はカバーできる。

🎯 最後にもう一度: 3 行で理解する外部キー

1. 定義: 「列の値が、 別テーブルの主キーに存在する (または NULL)」ことを DB に保証させる制約。 数式では $\forall t \in R: t[F]=\mathrm{NULL} \lor \exists s \in S, s[K]=t[F]$。
2. SSDSE での実例: pop.Code が pref.Code (47 県マスタ) を参照。 存在しない R99999 は IntegrityError で即拒否、 親削除は CASCADE/SET NULL/RESTRICT で挙動を選択。
3. 実務の鉄則: マスタは RESTRICT、 子の FK 列には INDEX、 sqlite では PRAGMA を毎回 ON、 クラウド DWH では宣言だけでなく dbt test 等でパイプライン側にもアサーションを入れる。

🐍 sqlite3 で SSDSE-B の参照整合性を CASCADE / SET NULL / RESTRICT 比較

🎯 このコードでやること: SSDSE-B-2026 を「都道府県マスタ pref」と「人口統計 pop」に分割し、 pref の 1 行を削除したときに 3 種類の ON DELETE 句で挙動が変わることを sqlite3 で実測する。 PRAGMA foreign_keys=ON の罠も含む。

📥 入力例 (SSDSE-B-2026 を正規化、 抜粋):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

import sqlite3, pandas as pd

df = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
pref = df[['Code', 'Prefecture']].drop_duplicates()
pop  = df[['SSDSE-B-2026', 'Code', 'A1101']].rename(
           columns={'SSDSE-B-2026': 'year', 'A1101': 'Pop'})

for fk_clause in ['RESTRICT', 'CASCADE', 'SET NULL']:
    con = sqlite3.connect(':memory:')
    con.execute('PRAGMA foreign_keys = ON;')          # 🚨 sqlite は毎回 ON 必須
    con.execute('CREATE TABLE pref(Code TEXT PRIMARY KEY, Prefecture TEXT)')
    con.execute(f'''CREATE TABLE pop(
        year INT, Code TEXT, Pop INT,
        FOREIGN KEY(Code) REFERENCES pref(Code) ON DELETE {fk_clause})''')
    pref.to_sql('pref', con, if_exists='append', index=False)
    pop.to_sql('pop',  con, if_exists='append', index=False)
    before = con.execute('SELECT COUNT(*) FROM pop WHERE Code="R31000"').fetchone()[0]
    try:
        con.execute('DELETE FROM pref WHERE Code="R31000"')   # 鳥取県を削除
        con.commit()
        after_pop  = con.execute('SELECT COUNT(*) FROM pop WHERE Code="R31000"').fetchone()[0]
        after_null = con.execute('SELECT COUNT(*) FROM pop WHERE Code IS NULL').fetchone()[0]
        print(f'{fk_clause:9s}: 鳥取行 before={before} after={after_pop} null化={after_null}')
    except sqlite3.IntegrityError as e:
        print(f'{fk_clause:9s}: 削除拒否 → {e}')
    con.close()

📤 実行例:

💬 結果の読み方: RESTRICT は親の不可触性を守り (推奨デフォルト)、 CASCADE は 12 行の鳥取統計が 連鎖消滅、 SET NULL は行は残るが Code が NULL になり 孤児化する。 sqlite では PRAGMA foreign_keys=ON を 接続ごとに毎回発行しないと外部キーが完全に無効化される (=制約を書いたのに何故か効かない最大の落とし穴)。

⚠️ よくある落とし穴

この用語を使うときに初学者が踏みやすい失敗パターン。 1 度経験してしまえば次から避けられますが、 先に知っておくに越したことはありません。

⚠️ 追加の落とし穴 (実務でよく出会う 7 件)

• 循環参照: A 表が B 表を参照、 B 表が A 表を参照する設計。 行を 1 件も挿入できなくなる。 解決策は 片方を NULL 許容にして後から UPDATE、 または DEFERRABLE INITIALLY DEFERRED で commit 時チェックに変える。
• 遅延制約の罠: PostgreSQL の DEFERRABLE は便利だが、 アプリ側が「挿入後即 SELECT で確認」する設計だと、 commit 前の不整合な中間状態が見えてしまう。
• NULL の伝播: FK 列は通常 NULL を許容するため、 「県不明」レコードが集計時に欠落することがある。 COUNT(*) と COUNT(code) の差で気づくが、 平均値計算では 静かに分母が変わって統計が歪む。
• 大規模 DELETE の CASCADE 爆発: 親 1 行に子 1 億行ぶら下がる状態で DELETE FROM pref WHERE code='R13000' を実行すると、 トランザクションログが膨張し DB が停止寸前まで追い込まれる。 大量削除は 子側から手動でバッチ削除するのが鉄則。
• FK と性能: FK は内部的にインデックス検索を伴うため、 子側に対応するインデックスがないと INSERT/DELETE が線形時間に劣化する。 PostgreSQL/MySQL は FK 列に自動でインデックスを張らないので、 設計時に必ず CREATE INDEX ON pop(code) を入れる。
• シャーディング環境では FK が効かない: 親と子が別シャードに分散していると、 RDBMS は FK 制約を保証できない (Vitess、 CockroachDB の一部設定など)。 アプリ層で「整合性キーは同一シャードに置く」設計を強制する必要がある。
• FK を「外す」分析用ミラー: 分析用 DWH では性能優先で FK を外すことが多いが、 そこから生まれた孤児行を「正常データ」と誤解して報告書に載せると、 後で「実は存在しない県」を含む集計だったと発覚する。 ETL の境界で validate 系の検査を入れる。

⚠️ 外部キー列に INDEX を貼り忘れる: 親削除が O(|R|) に劣化する罠

主キー側 (pref.Code) は自動で INDEX が貼られるが、 子側 FK 列 (pop.Code) には自動 INDEX が貼られない DB が多い (MySQL InnoDB は例外的に自動)。 これを忘れると、 親レコード 1 行を削除するたびに 子テーブル全件スキャンが走り、 pop が 100 万行なら 1 削除に数秒を要する事故が起きる。

SSDSE-B 級なら一瞬だが、 実務では 外部キーを宣言したら必ず CREATE INDEX idx_pop_code ON pop(Code) を併設する。 削除性能だけでなく、 結合 pref JOIN pop USING(Code) も大幅に高速化される。 dbt なら {{ config(indexes=[{'columns': ['Code']}]) }} でモデル定義に含められる。

確認の鉄則: 「EXPLAIN ANALYZE DELETE FROM pref WHERE Code='R31000' で子側がインデックス使用か (=Index Scan) を確認」「DWH (BigQuery / Snowflake) では物理 INDEX 不要だがクラスタリングキーで代替」「sqlite では EXPLAIN QUERY PLAN で USING INDEX 文字列を探す」。

🌐 関連手法・派生

この用語の周辺にある、 セットで覚えておきたい関連手法を整理しました。 状況によって使い分けが必要なので、 「強みと弱み」を 1 行で言えるようにしておくと、 場面に応じた選択が可能になります。

上の表に並べた 主キー・一意制約・チェック制約・カスケード は、 いずれもこの用語と隣り合う選択肢です。 右列に書いたのは「どこが違うか」なので、 違いがぴんと来ない行から先に読むと、 差分だけを追えて手戻りがありません。 リンクの無いものは本サイトに個別ページを設けていないため、 関連グループ教材か外部資料を当たってください。

⚖️ 似た用語との使い分け

「外部キー」と隣接する手法を、 ざっと俯瞰できる比較表として再整理します。 場面に応じてどれを採用するか、 まずは「適用条件」「仮定」「強み・弱み」の 3 軸で見比べてください。

「とりあえずデフォルト」で進めてしまうと、 適用条件外でも気付かず使い続ける事故になりがちです。 1 度「なぜこれを選んだか」を 1 文で書く習慣をつけると、 後の説明・査読でも強力な武器になります。

🛠 現場でのワークフロー例

「外部キー」を実際の分析プロジェクトに組み込むときの典型的な作業順序を示します。 教科書の例題と違って、 実データ・実業務では準備と検証に多くの時間を使うことに注意。

データエンジニアリング カテゴリのほかの用語と組合せて使う場面が多いため、 上記④までで終わらせず、 ⑤⑥まで丁寧に進めることが「結果が伝わる分析」の鍵です。

🔭 立場で変わる「外部キー」の見方

同じ 外部キー でも、 どの立場から読むかで「まず気にすること」が入れ替わります。 下の表は、 立場ごとにこのページのどこから読むと近道かを整理したものです。

本ページはすべての立場を意識して構成されていますが、 自分の関心に応じてセクションを取捨選択して読むのが現実的です。

📜 歴史と背景

「外部キー」の概念は突然生まれたものではなく、 関連する基礎理論・先行研究・実務的ニーズが積み重なって今の形になっています。 厳密な年表ではなく、 全体観をつかむためのざっくりした流れを示します。

歴史を知っておくと、 「なぜこの用語がこの定義になっているのか」「なぜ似た用語が複数あるのか」が腑に落ちやすくなります。 用語が生まれた動機を理解することが、 応用する力を養う近道です。

📔 ミニ用語集

「外部キー」を読み解く上で出てきた周辺の小用語を、 すぐに引けるよう 1 か所に集めました。 各説明は本ページの記述と整合しています。

親テーブル

参照される側（例: customers）

子テーブル

参照する側（例: orders）

PK

主キー（参照先）

FK

外部キー（参照元）

CASCADE

親の削除に連鎖して子も削除

🌐 外部キー vs 代替手段

💡 「分析用 DWH (BigQuery 等) は FK 宣言できても強制しない」 ことを知っておくと、 「FK あるから安心」と油断せず、 dbt test や Great Expectations のようなパイプラインアサーションを必ず併用するようになる。

🏗 ER 設計の現場手順: SSDSE-B-2026 を 4 表に正規化する完全例

112 列の SSDSE-B-2026 を「単一テーブルのまま」扱うと、 都道府県名やコードが 564 行に重複 して保存されることになります。 これは更新異常 (Update Anomaly) の温床。 第三正規形 (3NF) まで分解すると、 次の 4 表に整理できます。

この 4 表構成のメリット: 都道府県名を変更したいときに 1 行 (pref) だけ書き換えれば全テーブルに自動波及 する (FK で論理的に結ばれているため)。 「沖縄」を「沖縄県」に統一する程度の小修正でも、 非正規化テーブルなら 564 行を一括 UPDATE する必要があり、 途中で失敗すると部分更新で不整合が発生します。

🌐 dbt / Great Expectations / pandera による「FK の現代的代替」と SSDSE-B-2026 での実装比較

現代の分析基盤 (BigQuery / Snowflake / Redshift / Databricks) は FK 制約を強制しないのが標準である。 理由は (1) 追記型ワークロードで INSERT 性能を最優先、 (2) 分散ストレージで親子整合チェックがコスト高、 (3) スキーマ変更が頻繁。 では参照整合性をどう担保するか? 答えは 「DB 層でなく ETL/ELT パイプライン層でテスト化する」。 SSDSE-B-2026 を題材に 3 つのツールで実装を比較する。

このコードでやること: SSDSE-B-2026 を pandas DataFrame として扱い、 pandera で「人口表 pop の Code 列は pref マスタの Code 一覧に含まれていなければならない」というルールを宣言・検証する。

📥 入力データ (SSDSE-B-2026 から pref と pop を抜粋):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

import pandas as pd
import pandera as pa
from pandera import Column, DataFrameSchema, Check

pref = pd.DataFrame({
    'Code': ['R01000', 'R13000', 'R27000', 'R47000'],
    'Prefecture': ['北海道', '東京都', '大阪府', '沖縄県']
})
pop = pd.DataFrame({
    'Code': ['R01000', 'R13000', 'R99999'],
    'Year': [2020, 2020, 2020],
    'Pop': [5224614, 14047594, 123456]
})

valid_codes = set(pref['Code'])
pop_schema = DataFrameSchema({
    'Code': Column(str, Check.isin(valid_codes), description='pref への外部キー相当'),
    'Year': Column(int, Check.in_range(1975, 2030)),
    'Pop':  Column(int, Check.ge(0))
})

try:
    pop_schema.validate(pop, lazy=True)
    print('整合性 OK')
except pa.errors.SchemaErrors as e:
    print('違反検出:')
    print(e.failure_cases[['column', 'check', 'failure_case']])

📤 実行結果:

💬 結果の読み方: pandera が「Code 列に pref に存在しない R99999 がある」と検出。 DB の FK と違い、 pandas DataFrame だけで完結する FK 相当の検査ができる。 ETL の前処理段階で投入前に弾けるので、 DWH を汚さない。 SSDSE-B-2026 のような公的データを取り込む際の標準的なバリデーション層として推奨される。

現代の二段構え運用: (1) OLTP (運用 DB) では PostgreSQL/MySQL で FK 制約を厳格に張り、 アプリの不整合バグを未然に防ぐ。 (2) OLAP (分析 DWH) では FK を貼らず、 dbt/pandera/Great Expectations で「テストとして」検査する。 (3) 両者の橋渡し ETL 層で pandera を挟むことで、 OLTP の制約と OLAP のテストを連動させる。 この三層構造が 2026 年現在のベストプラクティスであり、 SSDSE-B-2026 のような公的データを分析する場面でも、 まず pandera Schema で取り込み、 次に dbt model で集計、 最後に Great Expectations で本番投入前の最終チェックという流れが定番化している。

関連用語の整理: 参照整合性 (本ページの中核概念) | 主キー (FK が参照する先) | 候補キー (PK 候補となる列) | 複合キー (FK でも複合化可能) | サロゲートキー (自然キー vs 代替キー) | インデックス (FK 列の必須付帯) | 正規化 (FK が分割表を繋ぐ) | 非正規化 (DWH で FK を捨てる戦略) | トランザクション (DEFERRED 制約と連動) | ACID (FK は一貫性 C を担保) | ELT (FK 代替テストの主戦場) | dbt (relationships test) | Great Expectations (Expectation Suite) | pandera (Schema validation) | SSDSE (本ページの例題データ)。

🔗 関連用語（前提・並列・発展）

この用語と直接つながる前後関係。 「前提」=先に知っておくと理解が早い／「並列」=同じ問題に別の角度／「発展」=応用・拡張版。 学習計画の参考にどうぞ。

• 🔗 主キー — 参照先
• 🔗 データベース — 前提
• 🔗 JOIN — 応用
• 🔗 正規化 — 設計理論

✅ 実務チェックリスト

分析を提出する前に、 以下を順に確認すると見落としが大きく減ります。 教材として身につけたい「思考の型」でもあります。

• □ 「外部キー」を使う場面かを再確認したか（適用範囲外で無理に使っていないか）
• □ データの尺度・分布・サンプル数を確認したか
• □ 前提条件を満たしているか（独立性・正規性・線形性など）
• □ 欠損値・外れ値の扱い方針が明確か
• □ 計算した値だけでなく不確実性（標準誤差・信頼区間）も把握したか
• □ 結果の解釈と限界を区別したか
• □ 再現性のためにパッケージバージョン・乱数シードを記録したか
• □ 関連グループ教材で全体像を確認したか

❓ よくある質問（FAQ）

📝 レポートでの報告

「外部キー」を用いた分析を文書化する際、 以下の項目を順序立てて記述すると、 読み手が結果を追体験しやすくなります。 学術論文でも実務レポートでも基本構造は共通です。

• 使ったデータ：出典（例: SSDSE-B-2026）、 期間、 サンプル数 n、 取得日
• 前処理の方針：欠損補完、 外れ値処理、 単位統一、 変数変換（対数、 標準化など）
• 適用条件の確認：本用語の前提が満たされているかを明示的に検証した結果
• 推定値：点推定だけでなく、 標準誤差・95% 信頼区間・p 値などの不確実性も併記
• 結果の可視化：図のキャプションに n・期間・変数の単位を含める
• 解釈：「何を意味するか」を、 ドメイン知識と結びつけて記述
• 限界：「何を意味しないか」を率直に書く（相関は因果ではない、 標本の偏り、 時期の特殊性など）
• 再現性：使用パッケージのバージョン、 乱数シード、 解析コードへのリンク

この型に沿うことで、 査読・上司・将来の自分の誰が読んでも追跡できる記述になります。

🔗 関連用語 (拡張)

• 主キー (Primary Key) — FK が指す先。 NOT NULL + UNIQUE が必要
• RDB — FK 制約の文脈となる関係モデル
• RDB 詳細 — トランザクション・分離レベル
• データクレンジング — 孤児行の発見と処理
• データエンジニアリング — ETL 全体像
• NoSQL — FK が無い世界での整合性管理
• JSON — 半構造化データへの FK 相当 (参照)
• 検証用データ — データ品質保証の延長線

📚 さらに学ぶための入口

本ページは初学者向けの導入に重きを置いています。 もう一段深く学びたい方向けの参考方向性を以下にまとめました。 具体的な書誌情報は出典を確認の上で各自で取得してください。

• 大学教科書レベル：Elmasri & Navathe『Fundamentals of Database Systems』第 3-9 章 (関係モデル・正規化・整合性制約) と Garcia-Molina-Ullman-Widom『Database Systems: The Complete Book』第 7 章 (Constraints and Triggers) で外部キーの理論基盤を学べます。
• 専門書：C.J. Date『SQL and Relational Theory』第 11 章で参照整合性 (referential integrity) の代数的基礎、 Joe Celko『SQL for Smarties』で実務的な ON DELETE CASCADE/SET NULL/RESTRICT の使い分け、 Bill Karwin『SQL Antipatterns』で「外部キー無し設計」の弊害事例。
• 論文・サーベイ：Codd (1970)「A Relational Model of Data for Large Shared Data Banks」CACM が関係モデルと参照整合性の原典。 Date & Darwen『The Third Manifesto』(2006) が外部キー設計の理論的拡張を提示。
• 公的統計：SSDSE-B-2026 を「都道府県マスタ (47 行) ↔ 統計値テーブル (47 × N 列)」と「年次マスタ ↔ 時系列値」に正規化し、 prefecture_code を FK として MySQL/PostgreSQL に投入すると参照整合性の働きが体感できます。
• OSS ドキュメント：PostgreSQL 公式ドキュメント「5.4. Constraints / 5.4.5. Foreign Keys」、 MySQL 公式「13.1.20.5 FOREIGN KEY Constraints」、 SQLAlchemy ForeignKey と Django ForeignKey の ORM 実装が標準教材。
• 本サイトの再現論文：複数公的データを結合して分析する事例は、 論文一覧 の地域統計・パネルデータ系の記事を参照してください。

🎯 このページの要点（最終確認）

「外部キー」を 1 行で言える ように整理：

• カテゴリ：データエンジニアリング
• 何をする道具か：外部キーは、 あるテーブルの列が別テーブルの主キーを参照することで、 テーブル間の関連と整合性を保証する仕組み。
• 使う前に必ず確認：適用条件、 サンプル数、 前提仮定
• 結果と一緒に必ず示す：不確実性（標準誤差・信頼区間）、 解釈、 限界
• 関連グループ教材：このページ末尾のリンクから全体像へ

🧭 次に読むなら：土台が怪しいと感じたら 主キー (Primary Key)・RDB へ戻り、 もう一段進みたければ RDB 詳細・データクレンジング へ進んでください。 この用語集は必要になった時に開く前提で作っているので、 今すぐ全部読む必要はありません。

📚 関連グループ教材

この用語の全体像を学ぶには、 横断的な教材から入るのが効率的：

• 📚 データエンジニアリング — 領域全体
• 📚 データ処理 — 結合操作

🗺 外部キー設計の意思決定フローチャート (SSDSE-B-2026 を題材に判断軸 6 つ)

「FK を貼る/貼らない」「ON DELETE を何にする」「INDEX を併設するか」は、 6 つの判断軸を順に問うフローで決められる。 SSDSE-B-2026 の pref → pop 関係に各軸を当てはめながら最終設計まで落とし込む。

最終設計案 (SSDSE-B-2026 sqlite):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

PRAGMA foreign_keys=ON;

CREATE TABLE pref (
    Code TEXT PRIMARY KEY,
    Prefecture TEXT NOT NULL
);

CREATE TABLE pop (
    Code TEXT NOT NULL,
    Year INTEGER NOT NULL,
    Pop  INTEGER CHECK (Pop >= 0),
    PRIMARY KEY (Code, Year),
    FOREIGN KEY (Code) REFERENCES pref(Code)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
);

CREATE INDEX idx_pop_code ON pop(Code);
CREATE INDEX idx_pop_year ON pop(Year);

💬 設計の読み方: 6 軸の判断結果が DDL のすべての要素に対応している。 PRAGMA は事故 A 対策、 PRIMARY KEY (Code, Year) は複合キー化、 FK 形式は NO ACTION で安全側、 DEFERRABLE はバルクロード対応、 2 つの INDEX は結合・集計性能。 これが「SSDSE-B-2026 の人口表」を長期運用するための過剰でも不足でもない最小設計である。 業務 DB ではここに監査列 (created_at, updated_at) や論理削除フラグ (deleted_at) を追加するが、 公的データ分析用途では不要。

このフローの汎用性: SSDSE-B-2026 以外のあらゆる外部キー設計に同じ 6 軸が使える。 例えば「ECサイトの注文 → 商品マスタ」なら、 軸 2 (商品は廃番で消える) → CASCADE は NG (注文履歴消失)、 軸 3 (商品コード書換あり) → ON UPDATE CASCADE 検討、 軸 4 (注文 INSERT 高頻度) → DEFERRED 不要、 と判断が変わる。 つまり FK 設計は対象データの性質を理解しているかのリトマス試験紙となる。

初学者の最頻 NG パターン: (a) 全 FK を CASCADE にする (連鎖事故)。 (b) INDEX を忘れる (性能事故)。 (c) sqlite で PRAGMA を忘れる (制約が無効)。 (d) NOT IN で孤児検査する (3 値論理事故)。 (e) DWH に FK を貼って INSERT 性能を落とす。 これら 5 つを避けるだけで、 外部キー絡みの本番事故の 9 割は予防できる。 SSDSE-B-2026 の小規模データで試行錯誤して、 5 パターンを自分の手で壊して直すのが最短の習得経路。

📊 外部キー運用の歴史的経緯と SSDSE-B-2026 で読む「RDBMS の哲学」

外部キーは 1970 年の Codd 論文で「参照整合性 (referential integrity)」として理論化され、 1986 年の SQL-86 規格でようやく明文化、 1992 年の SQL-92 で ON DELETE/UPDATE 句が追加された。 つまり「FK を貼る」という発想は RDBMS の根本思想そのものであり、 単なる制約構文ではない。 SSDSE-B-2026 のような公的データを RDBMS で扱う時、 FK を貼ることは Codd の正規化理論を実装に落とす行為に等しい。

歴史から学ぶ実践指針: (1) FK は 50 年以上の歴史を持つ枯れた技術であり、 OLTP では使わない理由がない。 (2) DWH では「FK の思想を保ったままテストに置き換える」のが正解で、 FK を完全に捨てるのではなく 別レイヤで担保する。 (3) SSDSE-B-2026 のような公的データを扱う研究・教育用途では、 sqlite + FK 制約 ON が最も学びが深い。 制約違反エラーに毎回ぶつかることで、 データ構造の理解が加速する。 (4) Codd の理論を 1970 年に遡って読むと、 FK が単なる「便利機能」ではなく 「データを正しく表現するための必須インフラ」として設計されたことが分かる。

SSDSE-B-2026 を題材に学ぶ意味: 47 都道府県 × 12 年という程よく小さく、 程よく現実的なデータは、 FK 5 形式・DEFERRED・孤児検出・代替テストのすべてを実機で確かめられる絶好の素材。 商用 DB の数十億行を相手に試行錯誤するより、 SSDSE で 100 回壊して 100 回直す方が習熟が速い。 この経験を積んだ後で本番 DWH に向き合うと、 dbt の relationships test や pandera の Check.isin が「FK を別の形で実装している」だけだと自然に見えてくる。 つまり FK の理解は RDBMS から Modern Data Stack まで一貫して使える普遍知識であり、 一度身につければ 10 年陳腐化しない。

🧭 まとめ: SSDSE-B-2026 で外部キーを「使いこなす」3 行サマリ

外部キーは 「親に実在することを保証する制約」であり、 SSDSE-B-2026 の pop.Code → pref.Code のように親子関係を持つすべての設計で出発点となる。 学習時には sqlite + PRAGMA + 5 形式の挙動比較を実機で体感し、 業務 OLTP では PostgreSQL/MySQL で厳格運用、 分析 OLAP では dbt/pandera/Great Expectations による「テストとしての FK」に置き換えるのが 2026 年の標準である。

本ページで扱った要点を 3 行で要約すると: (1) 形式選定: SSDSE 級マスタは NO ACTION + DEFERRED + 明示 INDEX が無難。 (2) 事故予防: PRAGMA 忘れ・型不一致・後付け FK の 3 事故を SSDSE で再現し体得。 (3) 現代的代替: DWH では FK を貼らず dbt の relationships や pandera の Check.isin でテスト化。 これら 3 つを SSDSE-B-2026 で実機検証すれば、 本番 DB でも応用可能な土台ができる。 47 都道府県 × 12 年のデータは「程よく小さく、 程よく現実的」で、 FK 学習に最適な素材である。 さらに本ページの 8 つの拡充ブロックを順に手元 sqlite で実行することで、 5 形式の挙動・孤児検出 SQL・理解度チェック 12 問・3 事故再現・pandera 代替・設計フローチャート・歴史的経緯を一気通貫で身につけられる構成となっている。

🖼 視覚的理解 (3 図)

外部キー (Foreign Key) を SSDSE-B-2026 都道府県データで可視化する。 親テーブル pref(Code) と子テーブル pop(Code, Year, Population) の関係を、 3 つの切り口 (散布図・カウント分布・地方別孤児件数) で点検する。

🔗 隣接手法への橋渡し

「外部キー」は単独で完結せず、 隣接する手法と接続することで分析パイプラインの一部として機能する。 以下に「上流→並列→下流」のパイプライン視点 と、 「接続 / 統合 / 比較」の関係視点 を両軸で示す。

• 上流（入力側で必要となる前処理・前提）: 主キー・関係データベース設計・第 3 正規形 (Codd 1971)
• 並列（同じ目的を持つ代替・競合手法）: 主キー・複合キー・代理キー (surrogate key)・JOIN 演算
• 下流（外部キー の結果を活用する後続手法）: 参照整合性制約 (ON DELETE CASCADE)・INNER / LEFT JOIN・ER 図

🔌 接続 — どの段階で何を渡すか

外部キーは 親テーブルの 主キー を参照する 子テーブル側の列として宣言される。 接続点は 2 か所:

• 宣言時: FOREIGN KEY (子列) REFERENCES 親テーブル(主キー列) で参照関係を定義 → DBMS が自動で整合性チェック
• 運用時: JOIN 演算で 外部キー = 主キー の条件で結合 → ER 図に矢印で可視化

🧬 統合 — 他の制約・機能と組み合わせる運用

外部キー単体ではなく以下のように 複合運用 するのが実務標準である。

• FK + NOT NULL: 必ず親が存在するなら NOT NULL、 任意なら NULL 許容 (1 対 0..1 関係)
• FK + ON DELETE CASCADE: 親削除時に子も自動削除 (注文→注文明細など)
• FK + INDEX: FK 列に index を張らないと JOIN が O(N×M) になる (PostgreSQL/MySQL は自動付与しない)
• FK + トランザクション: 親 INSERT → 子 INSERT を 1 トランザクションで包む (deferred constraint で順序を緩和)

⚖️ 比較 — 似たキー概念との違いと選び分け

「外部キー」は (1) 親テーブルの主キー特定 → (2) 子テーブルに FK 列追加 (FOREIGN KEY ... REFERENCES) → (3) ON DELETE / UPDATE (CASCADE / RESTRICT / SET NULL) 設定 → (4) JOIN テスト → (5) ER 図に明記、 の 5 段で運用する。

🌳 手法選択フロー

「外部キー」を実際の課題に当てはめるとき、 以下の 3 ステップで判断する。 領域固有の判断基準と組み合わせて使用する。

1. ステップ 1: 親テーブルの 主キーを特定
2. ステップ 2: 子テーブルに FK 列を追加 (FOREIGN KEY (col) REFERENCES ...)
3. ステップ 3: ON DELETE / UPDATE の挙動 (CASCADE / RESTRICT) を決定

正規化 (第 3 正規形) で参照整合性を担保するなら厳密な FK、 OLAP / DWH で性能優先なら soft FK (制約無し)、 NoSQL / 分散 DB なら application-level 整合性。 親子削除の挙動 (CASCADE) は業務ロジックで決定する。

🎮 触って理解する — 参照整合性と ON DELETE を体感する

外部キーの本質は 2 つの場面に現れます。 (1) 子の INSERT 時：親に存在しない値は拒否される、 (2) 親の DELETE 時：ON DELETE の宣言 (RESTRICT / CASCADE / SET NULL) で子の運命が決まる。 下の親テーブル (customers) と子テーブル (orders) を実際に操作して、 この 2 つを体感してください。 緑の線が「子 → 親」の参照リンクです。 親テーブルの行の ✕ ボタンで DELETE、 行の上にマウス (指) を動かすと参照関係がハイライトされます。 (正規化⇄JOIN の体験は 関係データベース のページにあります。 ここは「整合性の門番」としての FK に集中します。)

💡 試す順番のおすすめ： (1) C9 を INSERT → 拒否される。 (2) RESTRICT のまま C1 を ✕ → 拒否。 (3) CASCADE に切り替えて C1 を ✕ → 子 2 行が連鎖削除。 (4) リセットして SET NULL で C1 を ✕ → 子は残るが NULL に。 (5) C4 (子ゼロ) はどのモードでも消せることも確認。

🎨 何が起きているのか — 「親子の約束」を守る門番

外部キーとは「子テーブルの行は、 必ず実在する親を指す」という約束であり、 DB エンジンはその門番です。 門番の仕事は上で体験した通り 2 方向あります。 入口 (INSERT/UPDATE) では「親に無い値は通さない」、 出口 (DELETE) では「参照されている親を消すとき、 子をどうするかは事前の宣言 (ON DELETE) に従う」。 アプリのコードや手作業の CSV 編集では約束はいつか破られますが、 FK 制約はエンジンレベルで例外なく強制される — これが「アプリ側チェック」との決定的な違いです。

⚠️ よくある落とし穴 — CASCADE 事故と孤児行

• CASCADE 事故：上の実験で「親 1 行の削除で子 2 行が一瞬で消える」のを見た通り、 CASCADE は削除を増幅します。 FK が多段 (A→B→C) だと連鎖はさらに波及し、 「マスタを 1 行整理したつもりが履歴が数万行消えた」事故が実際に起こります。 マスタ表には RESTRICT / NO ACTION、 CASCADE は「親が消えたら子も無意味」な従属データ (注文→注文明細など) 限定が定石。
• 整合性なしの孤児行：CSV 同士の結合や、 FK 制約を張らない DWH / データレイクでは、 上で「拒否」された C9 のような孤児行が黙って生まれます。 孤児行は INNER JOIN で静かに脱落し (行数が減ったことに気づかない)、 LEFT JOIN では NULL が混ざって平均や合計を歪めます。 対策は「JOIN 前後で行数を必ず確認する」こと (データ結合 参照)。
• SET NULL の静かな副作用：行が残るので一見安全ですが、 集計時に GROUP BY customer_id で「NULL グループ」が出現し、 COUNT(customer_id) と COUNT(*) がズレます。 「誰の注文か分からない行」を残す意思決定は、 分析側と合意してから。

🚀 発展 — 遅延制約と、 分析前の孤児検出

遅延制約 (deferred constraint)：親子を同一トランザクションで同時に INSERT したい場合や循環参照がある場合、 PostgreSQL では DEFERRABLE INITIALLY DEFERRED を宣言すると FK チェックを COMMIT 時まで遅らせられます (SQLite では PRAGMA defer_foreign_keys=ON)。 「途中経過は違反していても、 取引の終わりに帳尻が合っていれば OK」という緩和です。

分析時の孤児検出：FK 制約のない CSV / Parquet を pandas で扱うときは、 自分が門番になる必要があります。 定番は anti-join (存在しない参照の抽出) です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

import pandas as pd

# customers / orders を用意する（都道府県を「顧客」、年次行を「注文」に見立てる）
_d = pd.read_csv('data/raw/SSDSE-B-2026.csv', encoding='cp932', skiprows=[1])
customers = (_d[_d['SSDSE-B-2026'] == 2023][['Code', 'Prefecture']]
             .rename(columns={'Code': 'id', 'Prefecture': 'name'}).reset_index(drop=True))
orders = (_d[['Code', 'SSDSE-B-2026', 'A1101']]
          .rename(columns={'Code': 'customer_id', 'SSDSE-B-2026': 'year', 'A1101': 'amount'})
          .reset_index(drop=True))

orphan = orders[~orders['customer_id'].isin(customers['id'])]
print(len(orphan))   # 0 でなければ参照整合性が壊れている
# merge の indicator を使う方法（left_only = 孤児行）
chk = orders.merge(customers, left_on='customer_id', right_on='id',
                   how='left', indicator=True)
print((chk['_merge'] == 'left_only').sum())

関連ページ： 主キー (参照される側の一意性) / 参照整合性 (制約の理論) / データ結合 (JOIN での実践) / 関係データベース (正規化⇄JOIN の体験) / SQL

🧠 解説の深化 — 「宣言なき外部キー」を見抜く目

ここまでは「FK を宣言して DB に守らせる」話が中心でした。 この節では視点を反転させ、 宣言される前から データの中に潜んでいる外部キー構造を、 SSDSE-B-2026 の実測値だけを使って掘り出します。 単一 CSV しか配られない分析コンペでも、 FK の目を持つ人はデータ品質チェックと省メモリ化の両方で差をつけられます。

🎨 直感 — 単一 CSV の中に外部キーは「折りたたまれて」いる

SSDSE-B-2026 は 1 枚の表 (実測: 564 行 × 112 列) ですが、 中身を測ると隠れた構造が見えてきます。

• Code は 47 種 (R01000〜R47000)、 年は 2012〜2023 の 12 種。 どの Code もちょうど 12 行ずつあり、 47 × 12 = 564 行 — 欠けゼロの完全格子。
• df.groupby('Code')['Prefecture'].nunique().max() は 1、 逆方向 (Prefecture→Code) も 1。 つまり Code と都道府県名は 1 対 1 の関数従属で、 都道府県名は 564 回も重複保存されている。

これはまさに「都道府県マスタ (47 行)・年マスタ (12 行) と事実表 (564 行) を JOIN し終えた結果」の形です。 正規化で分解した瞬間、 事実表の Code 列は都道府県マスタへの外部キーとして姿を現します。 FK は宣言によって生まれるのではなく、 データに内在する従属関係を DB に守らせる「契約書」にすぎない — この見方ができると、 FK の無い CSV でも「どこに暗黙の FK があるか」を先に洗い出す習慣がつきます。

⚠️ 落とし穴（重要） — 検査すべきは「子」だけではない

(1) 親キーの重複 → JOIN の行増殖。 FK 検査というと「子の値が親に存在するか (孤児チェック)」ばかり注目されますが、 対になる「親キーが一意か」が崩れた瞬間、 結合結果が静かに水増しされます。 実測デモ: SSDSE-B-2026 の 2023 年断面から作った 47 行の都道府県マスタに、 誤って東京都 (R13000) をもう 1 行重複登録してから 564 行の事実表と LEFT JOIN すると、 結果は 564 行 → 576 行 に増えます (東京都の子 12 行がそれぞれ 2 回マッチして +12 行)。 pandas なら validate 引数で即座に検出できます。

df.merge(pref_master, on='Code', how='left',
         validate='many_to_one')   # 親=一意 を宣言
# 親に重複があると即エラー (実測):
# MergeError: Merge keys are not unique in right dataset;
#             not a many-to-one merge

validate='many_to_one' は、 DB の「PK の一意性 + FK の多重度」を pandas の merge 1 行で検査する仕組み。 マスタと結合する merge には必ず付けるのが安全策です。

(2) NOT IN 孤児検査の 3 値論理事故。 本ページの NG パターン (d) で名前だけ挙げた罠の中身がこれです。 SQL で孤児行を WHERE Code NOT IN (SELECT Code FROM pref) と探すと、 親側の Code 列に NULL が 1 つでも混ざっていた場合、 x NOT IN (…, NULL) は全行 UNKNOWN と評価され、 結果は常に 0 件 = 「孤児ゼロ」と誤報します。 孤児がいてもいなくても 0 件なので、 検査した本人は気づけません。 対策は NOT EXISTS または LEFT JOIN … WHERE 親側 IS NULL に書き換えること。 pandas の ~isin() はこの罠を踏みません (NULL は単に不一致扱い) が、 SQL に翻訳した途端に発症する点が要注意です。

🚀 発展 — pandas の Categorical は「メモリ上の外部キー」

FK の発想は整合性の道具にとどまらず、 データ圧縮の基本原理でもあります。 pd.Categorical の内部は「カテゴリ一覧 (= 親マスタ)」と「そこを指す整数 codes (= 外部キー)」の 2 層構造そのもの。 SSDSE-B-2026 で実測すると:

• Prefecture 列 (564 行、 文字列) のメモリは 46,428 バイト。 astype('category') に変換すると 5,505 バイト (約 1/8.4) に縮む。
• 変換後の cat.codes の dtype は int8 — 47 都道府県なら 1 バイト整数で足りるため。 欠損値は code −1 で表され、 これは FK 列の NULL (参照先なし) に正確に対応する。

Parquet / Arrow の辞書エンコーディング (dictionary encoding) も同じ設計で、 「値の実体は親テーブルに 1 回だけ置き、 各行は整数キーで参照する」— つまり正規化 + 外部キーを物理層で実行しているわけです。 「FK = RDB の制約構文」と狭く覚えるのではなく、 「重複する実体を 1 か所に集め、 参照で指す」という普遍パターンとして理解すると、 DB 設計・pandas の省メモリ化・ファイル形式の選定が 1 本の線でつながります。

🔗 関連ページ

主キー (親側の一意性 — 本節 (1) の落とし穴はこの崩れ) / データ結合 (merge validate と行増殖の実践) / 参照整合性 (制約の理論) / 正規化 (関数従属からの表分解) / カテゴリ変数 (Categorical の統計的側面) / 関係データベース / SQL (NOT EXISTS への書き換え)