SQLite外部キー入門：REFERENCES・ON DELETE・PRAGMA

外部キーはテーブル同士をつなぐポインタ

SQLite における外部キー（foreign key）とは、あるテーブルの列の値が、別のテーブルの行と一致していなければならない、というルールを持った列のことです。リレーショナルデータベースでは、これを使って「この posts の行は、あの authors の行に属している」ということを表現します。著者の名前やメールアドレスを投稿のたびにコピーして持たせる必要はありません。

最小構成のサンプルを見てみましょう。親テーブルと子テーブルを外部キーでつないだ例です。

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('こんにちは', 1), ('メモ', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) で外部キーの宣言は完結します。意味はシンプルで、「このカラムには authors テーブルの id が入りますよ」ということ。これでデータベースは2つのテーブルが関連していることを認識し、外部キー制約が有効になっていれば、存在しない著者を指すINSERTを拒否してくれます。

sqlite 外部キーはデフォルトで無効

SQLite の外部キーについて、まず押さえておくべき最重要ポイントがこれです。みんな一度はハマります。SQLite は REFERENCES 句をパースはしますが、明示的に指示しない限り制約として効かせません。理由は歴史的な互換性で、外部キー機能が存在する前に作られた古いデータベースとの後方互換のためです。

外部キーが有効になっていないと、こうなります:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- author 999 は存在しないが、SQLite はこれを問題なく受け入れる:
INSERT INTO posts (title, author_id) VALUES ('孤児', 999);

SELECT * FROM posts;

孤立した行がそのまま入ってしまいました。本来期待する整合性チェックを効かせるには、接続のたびに最初に PRAGMA foreign_keys = ON; を実行する必要があります。

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('孤立した投稿', 999);

これでインサートは FOREIGN KEY constraint failed で弾かれるようになりました。ただし注意したいのは、この PRAGMA は接続ごとの設定で、データベースファイルには保存されないという点です。アプリケーション、CLI セッション、テストのフィクスチャ — どこでも毎回セットし直す必要があります。実運用のコードでは、接続を開いた直後に PRAGMA foreign_keys = ON; を実行するのが定番です。

REFERENCES 句の書き方と必要な条件

参照先のカラムは PRIMARY KEY か UNIQUE 制約付きでなければいけません。これがあって初めて、SQLite はルックアップが一意であることを保証できます。型についても揃えておきましょう。SQLite は型に寛容ですが、わざわざ混在させると思わぬ落とし穴にハマります。

外部キーの書き方は 2 通りあります。まずはカラムに直接インラインで書く方法:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

あるいは、テーブルレベルの制約として別に書く方法もあります。複数カラムにまたがる外部キーの場合は、こちらの書き方が必須になります。

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

どちらの書き方でも制約の内容は同じです。テーブルごとに読みやすい方を選んでください。

ON DELETE: 親が消えたとき子レコードはどうなる？

親行を削除したとき、その親を参照している子行をどう扱うか、SQLite は判断しなければなりません。この挙動は ON DELETE で指定します。

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Adaを削除したら、彼女の投稿2件も一緒に消えました。指定できる動作は次のとおりです。

• CASCADE — 子レコードも一緒に削除します。投稿に対する著者、注文に対する明細など、「親に従属する」データに向いています。
• SET NULL — 外部キー列をNULLにします。親が消えても子を残したいケース（削除されたユーザーのコメントを匿名として残す、など）で便利です。
• SET DEFAULT — 外部キー列を、宣言時に指定したデフォルト値に戻します。
• RESTRICT — 子レコードが1件でも残っていれば削除をブロックします。文の実行時点で即エラーになります。
• NO ACTION — デフォルトの挙動です。実質的にはほとんどのケースでRESTRICTと同じだと考えて構いません（チェックがコミット時まで遅延されるという違いはありますが、結果は同じで、宙ぶらりんの子レコードを残すことはできません）。

ON UPDATEは親キーの値が変更されたときに同じように作用しますが、主キーを更新する場面はそうそうありません。

foreign key constraint failed の原因と意味

このエラーが出るのは、主に2つの状況です。1つ目は、対応する親が存在しない値で子レコードをINSERTやUPDATEしようとしたときです。

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

著者ID 999 が存在しないか、カラムの型を取り違えているかのどちらかです。先に親レコードを INSERT するか、値を修正してください。

2 つ目は、外部キーが RESTRICT または NO ACTION の場合に、まだ子レコードが残っている親を DELETE（または UPDATE）しようとしたケースです。

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

子レコードを先に消してから親を削除するか、本当にカスケード動作が欲しいなら外部キーを ON DELETE CASCADE や SET NULL に変えておきましょう。

あまり見かけませんが、近い仲間に FOREIGN KEY mismatch というエラーもあります。これは参照先のカラムが主キーでもユニークでもない場合や、カラム数が一致しない場合に発生します。データの問題ではなく、スキーマ側の問題です。

既存テーブルへの外部キー追加

SQLite の ALTER TABLE は機能が限られていて、外部キー付きのカラムを新規追加することはできても、既存のカラムに外部キー制約を後付けすることはできません。定番の回避策は、テーブルをリネームして作り直す方法です。

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('こんにちは', 1), ('世界', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

この手順の流れはこうです。まず外部キーの強制を一旦オフにし、新しい制約を入れたテーブルを作り直してデータをコピー、古いテーブルを削除してリネームする。BEGIN/COMMIT で囲んでおけばこの一連の処理がアトミックになります。最後に外部キーを再度オンにすると、SQLite が既存の全行を新しい制約に対して検証してくれます。ただし、不正なデータがあった場合でもトランザクション自体はすでにコミット済みなので、心配なら事前にチェックしておきましょう。

マイグレーション後は PRAGMA foreign_key_check; を実行して、孤立した行が残っていないかを確認します。

実用的なスキーマ例

ここまでの内容をまとめて、親テーブル・子テーブル・多対多のタグ用中間テーブルを備えた小さなブログ用スキーマを作ってみます。

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

ここで押さえておきたいポイントは3つ。まず author_id が NOT NULL になっていること。つまり、どの投稿にも必ず著者が紐づきます。次に posts → authors の外部キーは CASCADE 設定なので、著者を削除するとその投稿もまとめて消えます。そして post_tags の中間テーブルは両サイド CASCADE。投稿かタグのどちらかを削除すれば、関連付けの行も自動でお掃除されます。

後で泣かないための習慣

• 接続するたびに PRAGMA foreign_keys = ON; を設定しましょう。「思い出したら有効化」ではなく、アプリのDBオープン処理に組み込んでしまうのが鉄則です。
• 外部キー列にはインデックスを張ること。SQLite は親側のキーには自動でインデックスを作りますが、子側には作りません。ON DELETE CASCADE は親が削除されるたびに子側を引きにいくので、ここがボトルネックになりがちです。
• ON DELETE の挙動はちゃんと選ぶこと。デフォルトの NO ACTION は安全ですが、データを整理しようとするたびに「foreign key constraint failed」に阻まれます。どう動いてほしいかを決めて、明示的に書きましょう。
• マイグレーションや一括インポートのあとは PRAGMA foreign_key_check; を流して、孤児レコードがバグになる前に見つけ出しておきましょう。

次回:INNER JOIN

外部キーは関係を「定義する」もので、実際にテーブルをまたいでデータを取ってくるのは JOIN の仕事です。次のページでは INNER JOIN を取り上げます。関連するテーブルから行を組み合わせて、それぞれから欲しい列だけを取り出す方法を見ていきましょう。