「えっ、SQLite3ってこんな仕様なの！？」と最近ビックリしたことを紹介します。
たとえばこんな2つのテーブルがあったとします。
CREATE TABLE blogs ( id int primary key, title varchar(32) ); CREATE TABLE comments ( id int primary key, content varchar(32), blog_id int, foreign key (blog_id) references blogs(id) );
ポイントはcommentsテーブルのblog_idにはblogs(id)への外部キー制約が貼ってあることです。
もちろん、blog_idもblogs(id)も、どちらもint型です。
で、以下のようなSQLを発行します（blog_idの値に注目）。
-- blogsにデータを追加 INSERT INTO blogs (id, title) VALUES (10, 'Hello'); --- commentsにデータを追加（blog_idの値に注目） INSERT INTO comments (id, content, blog_id) VALUES (20, 'Good!', 'XYZ');
さて、この実行結果はどうなるでしょうか？
僕は当然commentsテーブルへのINSERTは失敗するものと思っていました。
ところがどっこい、INSERTできてしまいました……。（blog_idに"XYZ"が入ってしまっている）
sqlite> .headers on sqlite> sqlite> SELECT * FROM blogs; id|title 10|Hello sqlite> sqlite> SELECT * FROM comments; id|content|blog_id 20|Good!|XYZ
なんでなん！？
ここが変だよSQLite3
SQLite3には実はこんな罠があります。
データ型が柔軟すぎる
SQLite3は"Flexible Typing"という方針で実装されており、INT型のカラムには以下のデータ型を保存することができます。
- INTEGER
- REAL
- TEXT
- BLOB
参考： The Advantages Of Flexible Typing
えっ、こんなのデータ型の意味ないやん……。
ちなみに上記の参考リンクには"Flexible Typing"のメリットやSQLite3がなぜ"Flexible Typing"を採用しているのか、といった説明がつらつらと書いてあります。 興味がある人は読んでみてください。（Google自動翻訳のリンクを載せておきます）
外部キー制約がデフォルトで無効になっている
仮にデータ型が柔軟だったとしても、外部キー制約がちゃんと機能していれば「ダメダメ、そんなデタラメなblog_idは保存できないよ！」とデータベースが怒ってくれそうです。が、SQLite3は怒ってくれません。
それもそのはず、なんとSQLite3はデフォルトで外部キー制約が無効になっているのです。（マジかよ）
Foreign key constraints are disabled by default (for backwards compatibility)
外部キー制約を有効にしたい場合は、DB接続後に PRAGMA foreign_keys = true を実行する必要があります。こうすればデタラメなblog_idを保存できなくなります。
sqlite> PRAGMA foreign_keys = true; sqlite> INSERT INTO comments (id, content, blog_id) VALUES (21, 'Good!', 'XYZ'); Runtime error: FOREIGN KEY constraint failed (19)
ちなみに外部キー制約が無効になっているのは後方互換性のため（for backwards compatibility）で、将来的にはデフォルトで有効になる可能性もあるそうです。
Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default.
余談：SQLite1やSQLite2って存在するの？
「普段は"SQLite3"っていう名前ばっかりよく聞くけど、"SQLite1"や"SQLite2"って聞いたことがないなあ」と思って調べてみたところ、いちおう1や2も存在しているようです。
- 2000-08-17：1.0 リリース
- 2001-09-28：2.0.0 リリース
- 2004-06-18：3.0.0 リリース
- 2023-05-16：3.42.0 リリース（2023年5月現在の最新版）
でもかれこれ19年ぐらいバージョン3なんですね。 そりゃ"SQLite3"しか聞いたことがないはずだわ〜。
まとめ
SQLite3はRuby on RailsのデフォルトのRDBMSなので、なんだかんだで10年ぐらい使っていることになります。 でも、こんな仕様になっているとは全然知りませんでした！ 今まで使ってきたRDBMS（PostgreSQLとか、MS SQL Serverとか）とは大きく仕様が違うのでビックリです。
データ型の強制や外部キー制約はデータの整合性を保つための重要な機能なので、やっぱりSQLite3は本番環境で使うRDBMSじゃないな、と改めて思いました（もともと使ったことないけど）。
とはいえ、どんなRDBMSでも「えっ、そんな仕様なの！？」って驚く部分は何かしらあるので、普段あまり使わないRDBMSを使うときは方言（？）に十分気を付ける必要がありますね。
