SQLite で外部キー制約が効かない（存在しない ID を INSERT できてしまう）

要約

REFERENCES を付けて外部キーを定義したのに、存在しない親の ID を持つ行がそのまま入る。ON DELETE CASCADE を書いても、親を消したときに子が消えない。

CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts(
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  title TEXT
);
INSERT INTO posts(user_id, title) VALUES (999, 'orphan');  -- エラーにならない

公式ドキュメントのとおり、SQLite の外部キー制約は後方互換のために既定で無効になっている。
有効にするには、接続ごとに次を実行する。

PRAGMA foreign_keys = ON;

有効な接続で同じ INSERT を実行すると、Python では sqlite3.IntegrityError: FOREIGN KEY constraint failed になる。

よくある原因

1. PRAGMA を実行していない: テーブル定義に外部キーを書いただけでは、制約は検査されない。
2. 接続ごとに実行していない: 設定はデータベースファイルではなく接続に付く。
   一度実行しても、別の接続や再接続した接続では OFF に戻る。
3. トランザクションの途中で実行した: 未コミットの書き込みがある状態で実行するとエラーは出ず、単に効かない。
   Python の sqlite3 は INSERT などの前に暗黙にトランザクションを開くので、書き込みの後に実行すると無視される。
4. autocommit=False で接続している: Python 3.12 で追加された autocommit=False では、sqlite3 が常にトランザクションを開いた状態を保つ。
   そのため、この接続で実行した PRAGMA foreign_keys = ON は効かない。
5. 違反した行がすでにある: 有効にしても、それまでに入った親の無い行は自動では検出されない。

解決策

1. 接続を開いた直後に有効にする

import sqlite3
 
 
def connect(path):
    con = sqlite3.connect(path)
    con.execute("PRAGMA foreign_keys = ON")
    return con

アプリの中で接続を作る処理をこの関数に集め、どの経路でも必ず通るようにする。
公式ドキュメントも、既定値に頼らずアプリケーション側で明示的に設定するよう勧めている。

2. 効いているかを確かめる

PRAGMA foreign_keys;

1 が返れば有効、0 なら無効だ。
何も返らない場合は、その SQLite が外部キーに対応しない設定でビルドされているか、3.6.19 より古い。

3. autocommit=False のときは切り替える前に設定する

con = sqlite3.connect("app.db", autocommit=True)
con.execute("PRAGMA foreign_keys = ON")
con.autocommit = False

autocommit を False に切り替えた時点でトランザクションが開くので、設定はその前に済ませる。

4. 既存の違反を洗い出す

PRAGMA foreign_key_check;

違反している行ごとに、子テーブル名・その行の rowid・参照先テーブル名・外部キーの番号が返る。
該当する行の親を補うか、行を削除してから運用を始める。