外部キーと参照整合性 ― 迷子のデータを防ぐ

導入

「この本の著者IDは 99 番」――でも著者テーブルに 99 番なんていない。こういう**存在しない相手を指す「迷子のデータ」**を防ぐのが、外部キー（FOREIGN KEY）と参照整合性です。DB 自身にルールを守らせれば、アプリのバグでおかしなデータが入るのを水際で止められます。

説明

外部キーは「この列の値は、必ず別のテーブルの主キーに実在すること」を DB に約束させる制約です。列 ... REFERENCES 相手テーブル(相手の列) で宣言します。

flowchart LR
    B["book.author_id"] -->|"REFERENCES author(id)<br/>実在する id しか許さない"| A["author.id"]

SQLite の注意: SQLite は外部キーの強制が既定でオフです。効かせるには、最初に PRAGMA foreign_keys = ON; を実行します（このコースの実行環境では毎回先頭で唱えてください）。

初期表示のコードは、author（著者）と book（本）を作り、book.author_id に外部キーを張っています。最後の INSERT は存在しない著者 99 を指すので、参照整合性に違反してエラーになり、その行は入りません。これが「迷子を防ぐ」働きです。

削除時の連動（ON DELETE）。「著者を消したら、その本も一緒に消したい／消させたくない」を指定できます。

author_id INTEGER REFERENCES author(id) ON DELETE CASCADE

ON DELETE CASCADE は「親（著者）を消したら、子（本）も連鎖して消す」。ON DELETE RESTRICT なら「子がいる親は消させない」。用途に応じて選びます。

やってみよう

初期表示のコードを実行してください。最後の行（著者99番を指す本）で FOREIGN KEY constraint failed のようなエラーが出れば成功です。参照整合性が働いた証拠です。次に、その最後の INSERT の 99 を 1（実在する夏目）に変えて実行すると、今度はエラーなく入ることを確かめましょう。

演習

まず PRAGMA foreign_keys = ON; を唱えたうえで、category2（id・name）と item（id・name・category_id が category2(id) を参照）を作ってください。そして category2 に何も入れないまま、item に category_id = 1 の行を入れて、参照違反のエラーになることを確かめてください。