できない.dev

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

SQLite の外部キー制約は既定で無効で、接続ごとに PRAGMA foreign_keys = ON を実行して初めて効く。
トランザクションの途中で実行すると黙って無視されるので、接続を開いた直後に実行し、PRAGMA foreign_keys で 1 が返ることを確かめる。

公開: 更新:

症状できない
python bad_default.py
PRAGMA foreign_keys -> 0
posts: [(1, 1, 'hello'), (2, 999, 'orphan')]

2026-10-25 に python:3.12(Python 3.12.15 / Git 2.47.3) で実際に打って取った出力。検証環境の詳細

要約

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 になる。

実行例

Python 3.12.15 の標準 sqlite3 では、PRAGMA foreign_keys が既定で 0 のため、存在しない親の ID を持つ行が入り、親を消しても子が残る。
PRAGMA foreign_keys = ON を実行しても、別の接続・書き込みの後の接続・autocommit=False の接続では 0 のままになる。
接続を開いた直後に実行すると FOREIGN KEY constraint failed になり、ON DELETE CASCADE も効く。
有効にする前に入った違反は PRAGMA foreign_key_check で行ごとに洗い出せる。

$ python bad_default.py
PRAGMA foreign_keys -> 0
posts: [(1, 1, 'hello'), (2, 999, 'orphan')]
$ python bad_pragma.py
接続 a で ON にした直後: 1
別の接続 b: 0
書き込みの後に ON にした接続 c: 0 in_transaction = True
autocommit=False の接続 e: 0
$ python solve_connect.py
PRAGMA foreign_keys -> 1
sqlite3.IntegrityError: FOREIGN KEY constraint failed
carol を消す前の posts: [(1, 1, 'hello'), (2, 999, 'orphan'), (3, 2, 'by carol')]
carol を消した後の posts: [(1, 1, 'hello'), (2, 999, 'orphan')]
$ python solve_autocommit.py
PRAGMA foreign_keys -> 1
$ python check.py
('posts', 1, 'users', 0)
('posts', 2, 'users', 0)

— 2026-10-25 時点の出力

検証環境Python 3.12.15 / Git 2.47.3 / 2026-10-25 検証
検証日
実行環境
python:3.12 Debian GNU/Linux 13 (trixie)
バージョン
  • Python 3.12.15
  • Git 2.47.3

この記事の「実行例」は、上記の環境で実際にコマンドを実行して得られた出力をそのまま掲載しています。 再現手順はリポジトリの検証スクリプトとして管理し、定期的に再実行して出力を更新しています。

解決策

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・参照先テーブル名・外部キーの番号が返る。
該当する行の親を補うか、行を削除してから運用を始める。

よくある原因

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