宣言しただけでは守られない — SQLite の外部キーは PRAGMA で有効化しないと強制されない

Claude の多段レビュー（7観点の並列レビュー＋反証検証）で浮かんだ「外部キーを宣言したのに強制されていなかった」話を、勉強のためにまとめたものです。

綴Photo のモバイル版は、写真と「綴り（気持ち）」をローカルの SQLite に保存している。テーブル定義の上では「綴りは必ずどれかの写真に属する」という外部キーの関係を宣言していた。ところがレビューで分かったのは、その宣言がただのコメントと同じ状態だったということ。SQLite は黙って何も強制していなかった。まとめておく。

そもそも『外部キー制約』って?

テーブル同士の「この行はあっちのテーブルのこの行に属する」という関係を、データベース自身に守らせる仕組み。

たとえば予約システムで「予約は必ず存在する会員のものでなければならない」としたいとします。予約テーブルの会員ID列に外部キー制約を張っておくと、存在しない会員IDで予約を入れようとした時点でデータベースが弾きます。アプリ側のコードがうっかりおかしなIDを書き込もうとしても、データベースが最後の砦になってくれます。

綴Photo では「綴り（気持ち）は必ずどれかの写真に属する」という関係がこれにあたります。綴りテーブルの写真ID列に、写真テーブルを指す外部キーを宣言していました。

コードで起きていたこと

そもそも『ORM』って?

ORM（Object Relational Mapper）は、データベースの操作を「SQL を直接書く」のではなく「プログラムのコードとして書く」仲介レイヤーです。

たとえばデータを取得したい時、素の SQL では SELECT * FROM meanings WHERE photo_id = 1 と書きます。ORM があると db.meanings.filter(photoId: 1) のように、データベースの言葉ではなくアプリのコードの言葉で書けます。ORM がそれを SQL に翻訳してデータベースに投げてくれます。

綴Photo のモバイル版では Drift という Flutter 向けの ORM を使っています。テーブルの構造（どんな列があるか / 外部キーはどれか）も、SQL のスキーマとしてではなく Dart のクラスとして書く形になっています。

ORM（綴Photo では Drift を使っている）のテーブル定義では、外部キーをこう宣言していた。

dart

// 綴りテーブルの定義（写真IDが写真テーブルを参照する）
class Meanings extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get photoId => integer().references(Photos, #id)(); // ← 外部キー宣言
  // ...
}

.references(Photos, #id) の部分が「この photoId は写真テーブルの id を指す外部キーだ」という宣言です。

ところが SQLite は、接続のたびに PRAGMA foreign_keys = ON を実行しない限り、この REFERENCES を一切強制しません。そして SQLite のデフォルトはこのスイッチが OFF になっています。Drift（正確には接続を作る drift_flutter）も、自動ではこのスイッチを入れてくれません。

なぜそうなりやすいか

これは SQLite の歴史的な経緯によります。外部キー制約は SQLite に後から追加された機能で、既存のアプリを壊さないために後方互換としてデフォルト OFF にされました。ONにすると、それまで素通りしていた不正な書き込みが急にエラーになり、古いアプリが動かなくなるからです。多くの他のデータベースでは外部キーは書けば効くので、その感覚で SQLite を触ると「書いたのに効いていない」という食い違いにハマります。

綴Photo の場合、今は綴りや写真を削除する機能が無いので、この問題は表に出ていなかった。だが将来「写真を削除する」「クラウドと同期する」といった機能を入れたとき、写真を消したのに綴りだけが残る、といった整合性の崩れが無警告で発生し得た。レビューはまだ顕在化していないこの穴を先回りで見つけた格好だ。

改善の方向

接続が開かれた直後に PRAGMA foreign_keys = ON を一度実行して、スイッチを入れる。Drift にはこの「DBを開いた直後に走る処理」を書く場所（beforeOpen）があるので、そこに置いた。

dart

MigrationStrategy get migration => MigrationStrategy(
  onCreate: (m) async => m.createAll(),
  onUpgrade: (m, from, to) async {
    // 既存のスキーマ移行処理（省略）
  },
  beforeOpen: (details) async {
    await customStatement('PRAGMA foreign_keys = ON'); // ← 外部キーを有効化
  },
);

ここで一つ注意点があります。綴Photo の過去のマイグレーションには、写真IDを別の写真に付け替えるデータ手術（重複した写真を1つに集約する処理）が含まれていて、その途中では一時的に外部キーの整合性が崩れる瞬間がある。もし外部キー強制をマイグレーション中に効かせると、この手術が途中でエラーになりかねません。

だが beforeOpen は マイグレーション（onUpgrade）が全部終わった後 に走ります。さらに Drift はマイグレーションの最中そのものでは外部キーを扱わない設計になっています。

もう一つ大事なのは、これは「挙動が変わる」変更だということ。今までスルーされていた不正な書き込みが、今後はエラーになります。だから入れる前にテストを全部通して、既存の正常な書き込みがどれもエラーにならないことを確認した上で入れた。「制約を厳しくする」変更は、それ自体がリグレッションを生む可能性があります。安全機能でも、足す前にテストの全通過を確認したのはそのためだ。

学びポイント

SQLite では「外部キーを宣言する」と「外部キーを守らせる」は別。PRAGMA foreign_keys = ON を接続ごとに実行しないと、REFERENCES はただのドキュメントになる
デフォルト OFF は後方互換のための歴史的経緯。他のDBの感覚で「書けば効く」と思い込むと食い違う
有効化は「挙動が変わる」変更。今までスルーされていた不正データが今後はエラーになるので、テスト全通過を確認してから入れる
「今は削除機能が無いから問題が出ていない」は「問題が無い」ではない。将来の機能で顕在化する穴は先に塞いでおける

この同じ綴りテーブルの外部キーには、もう一つ別の落とし穴があった。「外部キーの列にインデックスが自動で張られない」という話で、別記事「外部キーを張っても、索引は付いてこない」にまとめた。どちらも「スキーマに書いた／宣言しただけでは、思った挙動になっていない」という根は同じです。

この記事は2026年6月時点の自分の理解で書いています。仕様や認識が変わってる可能性があります。