実行できるSQLiteドキュメント: STRICTテーブル

Q: SQLiteのSTRICTテーブルとは何ですか?

STRICTテーブルは、宣言したカラムの型を厳格に守らせるテーブルです。例えばINTEGERと宣言したカラムには、整数かNULL以外の値は受け付けません。使うときはCREATE TABLEの閉じ括弧の後ろにSTRICTキーワードを付けるだけ。これを付けないと、SQLiteは従来の型親和性(type affinity)に従って、変換できる値は変換し、できない値はそのまま格納してしまいます。

Q: STRICTテーブルではどんな型が使えますか?

使えるのはINTEGER、REAL、TEXT、BLOB、ANYの5つだけです。通常のテーブルなら通るVARCHAR、DOUBLE、BOOLEAN、DATETIMEといったエイリアスは、STRICTテーブルではすべてエラーになります。ANYは例外的に、どんな型の値も変換せずにそのまま受け入れる「逃げ道」のカラム型です。

Q: 新しいSQLiteのデータベースではSTRICTテーブルを使うべき?

新規のスキーマなら、基本的に使うのをおすすめします。通常のテーブルでは見逃されてしまうバグ ── INTEGERカラムに紛れ込んだ文字列や、REALに誤ってシリアライズされたリストなど ── をSTRICTテーブルならその場で検出できます。コストはテーブルごとにSTRICTを1語書くことと、特殊な型名が使えなくなることだけ。SQLite 3.37(2021年リリース)以降で利用可能です。

STRICTテーブルが追加された背景

SQLiteの型の扱いがゆるいのは有名な話です。カラムをINTEGERで宣言したのに"hello"という文字列をINSERTしても、SQLiteは何食わぬ顔でそのまま文字列として保存してしまいます。この柔軟さは90年代に意図して設計されたものなんですが、PostgreSQLやMySQLから来た人にとってはかなり面食らう挙動ですし、バグの温床にもなります。

そこで登場したのがSTRICTテーブルです。SQLite 3.37で追加された機能で、テーブル単位でオプトインする形で有効化します。一度STRICTにしてしまえば、カラムの型は宣言どおりに厳密に扱われるようになります。

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

STRICT キーワードは閉じ括弧の後ろに置きます。それ以外は普通の CREATE TABLE と変わりません。違いがハッキリ出るのは、カラムに合わない型の値を入れようとした瞬間です。

STRICTテーブルが実際にチェックしてくれること

通常のテーブルでは型親和性(type affinity)が働き、値を宣言した型に変換しようと試み、変換できなければそのまま格納されます。一方、STRICTテーブルでは型が合わなければエラーになります。

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- OK
INSERT INTO accounts (balance) VALUES ('100');   -- OK、'100' はきれいに変換される
INSERT INTO accounts (balance) VALUES ('oops');  -- エラー

同じことを STRICT じゃない普通のテーブルでやると、3 回目の INSERT が普通に通ってしまいます。INTEGER と宣言したカラムに、SQLite はあっさり文字列 'oops' を入れてくれるんです。数か月後、集計クエリが意味不明な結果を返してきて、原因究明に半日溶かす——よくある話です。STRICT を使えば、INSERT した瞬間にエラーが出るので、その場で直せます。

実際に出るエラーはこんな感じです:

実行時エラー: TEXT 値を INTEGER カラム accounts.balance に格納できません

明確で、すぐ気づけて、見逃しようがない。

STRICTテーブルで使える5つの型

STRICTテーブルで指定できる型名は、次の5つだけです。

INTEGER — 整数。
REAL — 浮動小数点数。
TEXT — 文字列。
BLOB — 生のバイト列。
ANY — 任意の型。型変換は一切行われません。

これだけです。通常のSQLiteなら気軽に受け付けてくれる別名 — VARCHAR(255)、DOUBLE、BOOLEAN、DATETIME、INT など — は、STRICTテーブルの中ではすべてエラーになります。

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

エラー内容はこちら：

解析エラー: bad.name のデータ型が不明です: "VARCHAR(255)"

修正方法はシンプルで、5 つの標準型のいずれかを使えば OK です。VARCHAR(255) は TEXT、DATETIME も TEXT(SQLite はそもそも日付を ISO 文字列として保存します)、BOOLEAN は INTEGER(0 と 1 で表現)に置き換えます。

逃げ道としての ANY 型

STRICT テーブルの中で唯一、異なる型の値を混在させられるのが ANY 型です。キー/バリューテーブルの汎用的な value カラムなど、型を限定したくない場面で重宝します。

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANYはSTRICTテーブルの中では特別な扱いになります。同じキーワードでも他の場面とは違い、型変換を行わずに値をそのまま格納してくれるのです。'100'という文字列は文字列のまま、100という整数は整数のまま保持されます。先ほどのクエリで使ったtypeof()の結果がその証拠です。

これが非STRICTテーブルなら、ANY親和性のカラムは数値に見える文字列を勝手に数値へ変換してしまいます。STRICTなら元の型がそのまま残るというわけです。

STRICTテーブルとPRIMARY KEYの関係

ここで一つ細かい違いがあります。通常のテーブルではINTEGER PRIMARY KEYが特別扱いされ、rowidのエイリアスになり整数しか受け付けません。一方、それ以外の主キー宣言はかなり緩めです。

ところがSTRICTテーブルでは、主キーかどうかに関係なくカラムの型がきっちり強制されます。

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Widget', 9.99);
INSERT INTO products VALUES (42, 'Gizmo', 14.99);  -- 42 は '42' に変換される? いいえ — エラーになる。

SELECT * FROM products;

2回目のINSERTは失敗します。STRICTでない普通のテーブルなら、42はTEXT型の主キー列にこっそり格納されてしまいますが、STRICTテーブルではちゃんとエラーで知らせてくれます。

STRICTテーブルと通常テーブルの併用

STRICTの設定はデータベース全体ではなく、テーブル単位で効きます。同じファイルの中に、厳密なusersテーブルと、ゆるいeventsテーブルを共存させても問題ありません。外部キーも、通常のテーブル間と同じように普通に機能します。

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- 型が宣言されていない。affinity ルールが適用される
);

INSERT INTO users (name) VALUES ('Boris');
INSERT INTO events (user_id, payload) VALUES (1, 'login');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

eventsテーブルはSTRICTが付いておらず、payloadにも型を宣言していないので、何を入れても通ってしまいます。たまに便利ではありますが、デフォルトで使うのは危険です。型なしのカラムは「何でも入れたい」雑多な用途に限定しておくのが無難です。

STRICTテーブルを使うべき場面

新しくスキーマを設計するなら、答えはほぼ「常に使う」です。コストは小さく、テーブルごとにキーワードを1つ足し、5つの正規の型名を覚えるだけ。代わりに、本来ならデータの中に紛れ込んでいたはずのバグが、原因となったINSERTの時点で表に出てきます。

STRICTを使わなくてよいケース:

既存のスキーマが緩い型付けに依存している、古いSQLiteデータベースをメンテナンスしているとき。
SQLite 3.37(2021年10月)より古いバージョンを対象にしているとき。そもそもキーワードが存在しません。
1つのカラムに本当に複数の型を入れたいとき。ただしその場合でも、非STRICTテーブルにするより、STRICTテーブル+ANYカラムの組み合わせがおすすめです。こうすれば他のカラムの型チェックは効いたままになります。

通常のテーブルをSTRICTに変換するときのチェックリスト:

VARCHAR、CHAR、NVARCHARはTEXTに置き換える。
DOUBLE、FLOAT、NUMERICはREALに置き換える。
BOOLEAN、BIT、TINYINTはINTEGERに置き換える。
DATETIME、TIMESTAMP、DATEはTEXT(unixタイムスタンプとして保存しているならINTEGER)に置き換える。
閉じカッコの後ろにSTRICTを追加する。

次のテーマ: 主キー

STRICTテーブルは、カラムが値をどう格納するかを厳密にしてくれます。次に厳密にしておきたいのが、どのカラム が各行を識別するか、つまり主キーです。SQLiteの主キーには(特にINTEGER PRIMARY KEYとrowidまわりに)いくつか独特の挙動があるので、実際のスキーマを設計する前に押さえておく価値があります。

よくある質問

SQLiteのSTRICTテーブルとは何ですか?

STRICTテーブルは、宣言したカラムの型を厳格に守らせるテーブルです。例えばINTEGERと宣言したカラムには、整数かNULL以外の値は受け付けません。使うときはCREATE TABLEの閉じ括弧の後ろにSTRICTキーワードを付けるだけ。これを付けないと、SQLiteは従来の型親和性(type affinity)に従って、変換できる値は変換し、できない値はそのまま格納してしまいます。

STRICTテーブルではどんな型が使えますか?

使えるのはINTEGER、REAL、TEXT、BLOB、ANYの5つだけです。通常のテーブルなら通るVARCHAR、DOUBLE、BOOLEAN、DATETIMEといったエイリアスは、STRICTテーブルではすべてエラーになります。ANYは例外的に、どんな型の値も変換せずにそのまま受け入れる「逃げ道」のカラム型です。

新しいSQLiteのデータベースではSTRICTテーブルを使うべき?

新規のスキーマなら、基本的に使うのをおすすめします。通常のテーブルでは見逃されてしまうバグ ── INTEGERカラムに紛れ込んだ文字列や、REALに誤ってシリアライズされたリストなど ── をSTRICTテーブルならその場で検出できます。コストはテーブルごとにSTRICTを1語書くことと、特殊な型名が使えなくなることだけ。SQLite 3.37(2021年リリース)以降で利用可能です。