SQLite3 は外部キーの使用を「忘れる」

概要

私は SQLite3 で Ruby を使用していますが、Sqlite3 で外部キーを使用しようとしましたが、残念ながら成功しませんでした。 sqlite3 –version によると、バージョン 3.7.13 がインストールされています。私の知る限り、Sqlite3 はバージョン 3.6.x 以降外部キーをサポートしています。

外部キーはデフォルトで非アクティブ化されており、PRAGMA foreign_keys = ON; でアクティブ化する必要があることはわかっています。私の Ruby db-create-script では、次のようなことを行っています。

sql = <<-SQL
  PRAGMA foreign_keys = ON;
  CREATE TABLE apps (
    id ....
  );
  CREATE TABLE requests (
    ...
    app_id INTEGER NOT NULL,
    FOREIGN KEY(app_id) REFERENCES apps(id),
  );
  ...
SQL
db.execute_batch(sql)

残念ながら、不明なアプリ ID を持つリクエストに行を挿入することはできます。それは機能しますが、当然、そうすべきではありません。

興味深いことに、sqlite3 シェルを直接使用すると、次の動作が観察できます。

$ sqlite3 database.db
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1 // as expected
sqlite> .quit
$ sqlite3 database.db
sqlite> PRAGMA foreign_keys;
0 // off ?!

sqlite3 シェルを終了せずに、外部キーをアクティブ化した後 (シェルを終了しないで) 外部キーが機能し、不明な app_id を持つ行を挿入することはできません。

解決策

私は自分の質問に答えることができると思います：ドキュメントには次のように書かれています：外部キー制約はデフォルトで無効になっています（下位互換性のため）。そのため、データベース接続ごとに個別に有効にする必要があります。面倒ですが、ようやく使えるようになりました。