sqlite3のPRAGMA文で外部キー制約を有効にする

db

これまでsqlite3で外部キーを使ったことがなかったので知りませんでしたが、sqlite3はデフォルトでは外部キーのサポートが無効になっている事に気が付きました（バージョンは3.6.22）。テーブルのcreate時に外部キー制約を指定することはできるけれど、実際に参照先テーブルのレコードが変更、削除されたときはノーチェックです。不正なデータだろうがなんだろうがwarningひとつ出しません。

sqlite3で外部キーを使いたい場合、PRAGMA文で動作を変更しなければいけません。sqlite3はPRAGMA文で動作を変更できるらしいです。たとえばPRAGMA cache_sizeでキャッシュサイズを変更できます。PRAGMA文で使える項目のリストは以下のページに記載されています。
http://www.sqlite.org/pragma.html

PRAGMAで外部キー制約を有効にしたいときは「foreign_keys」にtrueを代入します。

PRAGMA foreign_keys = true

ただしPRAGMA foreign_keysによる設定はインタプリタ開始時に初期化されてしまうので、常に外部キー制約を使いたい場合はインタプリタ起動時に常に上の文を実行する必要があります。rubyやpythonのバインディングから使う場合もおそらく同様でしょう。試していませんが。

正直面倒臭すぎですが、参考ページによると外部キー制約は将来的にデフォルトで有効になるかもしれないそうです。将来的な変更にともなう問題を避けるためには、有効にするか無効にするかを明示的に設定してデフォルト設定で動作が変わらないようにするべきだそうです。

As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite. To minimize future problems, applications should set the foreign key enforcement flag as required by the application and not depend on the default setting.

以下にPRAGMA foreign_keysで外部キー制約を有効にした場合、無効にした場合の挙動の違いを示します。

SQLite ポケットリファレンス

SQLite ポケットリファレンス

SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> -- 二つのテーブルjob, personを作成;
sqlite> -- DDL発行時に外部キーの参照先レコードが削除されたときNULLを代入するよう設定;
sqlite> create table job (id integer primary key, name varchar(255));
sqlite> create table person (id integer primary key autoincrement, name varchar(255), job_id integer references job(id) on delete set null);
sqlite> insert into job values(1, 'job a');
sqlite> insert into job values(2, 'job b');
sqlite> insert into job values(3, 'job c');
sqlite> insert into job values(4, 'job d');
sqlite> -- 外部キー制約はデフォルトで**無効**のため、制約に反するデータを挿入できる;
sqlite> insert into person values (null, 'alice', 100);
sqlite> -- PRAGMA文で外部キー制約を有効化する;
sqlite> PRAGMA foreign_key=true;
sqlite> -- 制約に反するデータの挿入は失敗する;
sqlite> insert into person values (null, 'alice', 100);
Error: foreign key constraint failed
sqlite> -- 無効にする;
sqlite> PRAGMA foreign_keys=false;
sqlite> insert into person values (null, 'bob', 1);
sqlite> insert into person values (null, 'carol', 1);
sqlite> insert into person values (null, 'david', 3);
sqlite> -- 出力を見やすいよう設定;
sqlite> .mode column
sqlite> .width 8 8 8 8 8
sqlite> .header on
sqlite> -- 確認;
sqlite> select * from person;
id        name      job_id
--------  --------  --------
1         alice     100
2         bob       1
3         carol     1
4         david     3
sqlite> -- 制約に反するデータを削除;
sqlite> delete from person where name='alice';
sqlite> -- テスト;
sqlite> select * from person left join job on person.job_id = job.id;
id        name      job_id    id        name
--------  --------  --------  --------  --------
2         bob       1         1         job a
3         carol     1         1         job a
4         david     3         3         job c
sqlite> -- 外部キーを無効にして外部キーの参照先を削除すると・・・;
sqlite> PRAGMA foreign_keys=false;
sqlite> delete from job where id=1;
sqlite> -- 参照先テーブルのレコードは削除されているが・・・;
sqlite> select * from job;
id        name
--------  --------
2         job b
3         job c
4         job d
sqlite> -- 参照元テーブルのフィールドには影響なし！SET NULLされない;
sqlite> select * from person;
id        name      job_id
--------  --------  --------
2         bob       1
3         carol     1
4         david     3
sqlite> -- 削除したレコードを復活させる;
sqlite> insert into job values (1, 'job a');
sqlite> select * from job;
id        name
--------  --------
1         job a
2         job b
3         job c
4         job d
sqlite> -- 今度は外部キー制約を有効にして参照先レコードを削除すると・・・;
sqlite> PRAGMA foreign_keys=true;
sqlite> delete from job where id=1;
sqlite> -- 制約により、フィールドに正しくNULLが代入された;
sqlite> select * from person left join job on person.job_id = job.id;
id        name      job_id    id        name
--------  --------  --------  --------  --------
2         bob
3         carol
4         david     3         3         job c