実行できるSQLiteドキュメント: ROWID

Q: SQLiteのROWIDとは何ですか？

通常のSQLiteテーブルには、行を一意に識別するためのrowidという64bit符号付き整数のカラムが暗黙的に存在します。SQLiteは内部のB-treeストレージで、このrowidを実際のキーとして使っています。明示的に宣言していなくてもSELECT rowid, * FROM tと書けば取り出せます。

Q: ROWIDとPRIMARY KEYは何が違うのですか？

rowidは最初から自動で存在するもの、PRIMARY KEYは自分で宣言するものです。特殊なのがINTEGER PRIMARY KEYで、これを指定するとそのカラムがrowidの別名（エイリアス）になり、別カラムとして持つのではなくrowidそのものとして扱われます。一方で、TEXT型や複合キー、あるいはINTEGERではなくINT PRIMARY KEYと書いた場合などは、rowidとは別に格納されます。

Q: WITHOUT ROWIDを指定すると何が変わりますか？

WITHOUT ROWIDを付けると、SQLiteは隠しrowidを作らず、宣言したPRIMARY KEYをそのまま実際のストレージキーとして使います。整数以外のキーを使うテーブルでは容量を節約でき、検索も速くなることがあります。ただしlastinsertrowid()やインクリメンタルBLOB I/Oなど一部の機能が使えなくなるので、デフォルトで使うものではなく、目的を持って選ぶ機能だと考えてください。

どのテーブルにも隠れている秘密のカラム

SQLite で何の変哲もないテーブルを作ると、自分では宣言していないのに最初から備わっているカラムがあります。

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('1番目', 'こんにちは');
INSERT INTO notes (title, body) VALUES ('2番目', '世界');

SELECT rowid, title, body FROM notes;

rowid というカラムは実在します。普通のテーブルなら、こちらが何も指定しなくても SQLite が自動で割り当ててくれるんです。64 ビット符号付き整数で、テーブル内ではユニーク。SQLite が B-tree ストレージから行を引くときに実際に使っている、本物のキーです。テーブルの背骨、つまり他のすべてを支えているインデックスのようなものだと考えてください。

普段目にしないのは、SELECT * の結果に含まれないからです。見たいときは、明示的にカラム名を指定する必要があります。

ROWID には 3 つのエイリアスがある

他のデータベース向けに書かれた SQL でも rowid がよく登場するため、SQLite は同じカラムを指す名前として 3 つを受け付けています:

CREATE TABLE notes (title TEXT);
INSERT INTO notes VALUES ('a'), ('b'), ('c');

SELECT rowid, oid, _rowid_, title FROM notes;

rowid、oid、_rowid_ はどれも同じ隠しカラムを指しています。もしこれらと同じ名前で実際のカラムを定義した場合は、そちらが優先されてエイリアスは使えなくなりますが、引っかかるのはそこだけ。普段のコードでは rowid と書いておけば十分です。

カギを握るのは INTEGER PRIMARY KEY

ここが他のデータベースから来た人が必ずつまずくポイントです。カラムをぴったり INTEGER PRIMARY KEY として宣言すると、そのカラムは別途保存されるのではなく、rowid そのものに なります ：

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

INSERT INTO users (name) VALUES ('Ada'), ('Boris'), ('Rosa');

SELECT rowid, id, name FROM users;

rowid と id は、名前が違うだけで同じ列です。id を省略して INSERT すると、整数値が自動で割り振られます（だいたい最大の rowid + 1 になります）。これこそが、SQLite で自動採番の主キーを持たせるなら INTEGER PRIMARY KEY が一番効率的だと言われる理由です。余計な列もインデックスも要らず、rowid そのものを使い回しているだけだからです。

ただし、書き方には要注意。INT PRIMARY KEY は 同じではありません 。ここでは INT と INTEGER で挙動がはっきり変わります：

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INT PRIMARY KEY, name TEXT);

INSERT INTO a (name) VALUES ('x');
INSERT INTO b (name) VALUES ('y');

SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

テーブル a では id と rowid が一致します。一方、テーブル b の id はただの普通のカラムで、rowid は別途、内部に隠れた整数として存在します。さらに厄介なのは、b.id は INSERT 時に自動で値が入らず、自分でセットしない限り NULL のままだということです。エイリアスとして扱いたいなら、省略せずに INTEGER PRIMARY KEY と書くのが鉄則です。

INSERT した行の rowid を取得する

INSERT の直後には、割り当てられたばかりの rowid を知りたい場面がよくあります。たとえば子テーブルの行を紐付けたいときなどです。SQLite では last_insert_rowid() が用意されています。

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('1番目');
INSERT INTO posts (title) VALUES ('2番目');

SELECT last_insert_rowid();

この関数は、現在のコネクションで最後に成功した INSERT の rowid を返します。多くのデータベースドライバでは cursor.lastrowid のような形で同じ値が取れるようになっています。後ほど紹介する RETURNING 句を使えば、INSERT 文の中で直接取得することもできます。

ROWID は永続的ではない

行の rowid は、その行が存在している間は変わりませんが、一生ものの識別子というわけではありません。VACUUM を実行すると rowid が振り直されることがありますし、行を削除すれば、その番号は将来の INSERT で再利用される可能性があります。

CREATE TABLE items (name TEXT);
INSERT INTO items VALUES ('a'), ('b'), ('c');

DELETE FROM items WHERE name = 'c';
INSERT INTO items VALUES ('d');

SELECT rowid, name FROM items;

削除や VACUUM、エクスポートをまたいで生き残る識別子が欲しいなら、自前で INTEGER PRIMARY KEY カラムを宣言しましょう（これで値がその行に固定されます）。さらに、絶対に再利用されない単調増加の値が必要な場面では AUTOINCREMENT キーワードの利用も検討してみてください。

WITHOUT ROWID テーブルの使い方

rowid がただのオーバーヘッドにしかならない場面もあります。典型的なのは、本来のキーが整数じゃないケースです。たとえば名前をキーにした都市テーブルを作ると、内部的には rowid の B-tree と、プライマリキーを担保するための name のインデックスという、2つの構造ができてしまいます。WITHOUT ROWID を使えば、これをひとつにまとめられます。

CREATE TABLE cities (
    name       TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

INSERT INTO cities VALUES ('Lisbon', 545000), ('Paris', 2102000);

SELECT * FROM cities WHERE name = 'Lisbon';
-- SELECT rowid FROM cities;   -- エラー: そのような列はありません: rowid

これで name が実体としてのストレージキーになります。name での検索は間接参照が一段省け、テーブル自体も小さくなります。代わりにトレードオフがあります:

rowid、oid、_rowid_ は存在しません。これらのカラムそのものが無くなります。
このテーブルへの INSERT では last_insert_rowid() が更新されません。
インクリメンタル BLOB I/O や一部のレプリケーション機能が使えません。
テーブルには必ず PRIMARY KEY を宣言する必要があります。

WITHOUT ROWID はデフォルト設定ではなく、あくまで狙いを絞った最適化です。プライマリキーが整数以外で、かつテーブルが大きい、もしくは書き込みが多い場合に検討しましょう。普通の整数キーのテーブルなら、通常の rowid レイアウトのままで十分速いです。

全体像をシンプルにまとめる

要点だけ抜き出すとこうなります:

通常の SQLite テーブルには、rowid という 64bit 整数の隠しキーが必ず存在する。
INTEGER PRIMARY KEY(この綴りでないとダメ)を指定すると、そのカラムが rowid のエイリアスになる。
直前に割り当てられた値は last_insert_rowid() で取得できる。
rowid は削除後に再利用されることがあり、VACUUM で振り直されることもある。
WITHOUT ROWID テーブルは隠しキーを持たず、宣言したプライマリキーをそのまま使う。整数以外のキーで便利だが、いくつかの機能を失う。

普段は rowid のことを意識する必要はほとんどありません。id INTEGER PRIMARY KEY と書いて、番号付けは SQLite に任せておけば十分です。意識すべきなのは、ストレージをチューニングするとき、既存のスキーマを読み解くとき、あるいは「INT PRIMARY KEY と INTEGER PRIMARY KEY で挙動が違うのはなぜ?」と疑問に思ったときです。

次回: NOT NULL と DEFAULT

行の同一性についてはこれで片付きました。次のレイヤーは、残りのカラムにまともな値を入れさせる仕組みです。その役割の大半を担うのが NOT NULL と DEFAULT の 2 つの句で、次回はここを掘り下げていきます。

よくある質問

SQLiteのROWIDとは何ですか？

通常のSQLiteテーブルには、行を一意に識別するためのrowidという64bit符号付き整数のカラムが暗黙的に存在します。SQLiteは内部のB-treeストレージで、このrowidを実際のキーとして使っています。明示的に宣言していなくてもSELECT rowid, * FROM tと書けば取り出せます。

ROWIDとPRIMARY KEYは何が違うのですか？

rowidは最初から自動で存在するもの、PRIMARY KEYは自分で宣言するものです。特殊なのがINTEGER PRIMARY KEYで、これを指定するとそのカラムがrowidの別名（エイリアス）になり、別カラムとして持つのではなくrowidそのものとして扱われます。一方で、TEXT型や複合キー、あるいはINTEGERではなくINT PRIMARY KEYと書いた場合などは、rowidとは別に格納されます。

WITHOUT ROWIDを指定すると何が変わりますか？

WITHOUT ROWIDを付けると、SQLiteは隠しrowidを作らず、宣言したPRIMARY KEYをそのまま実際のストレージキーとして使います。整数以外のキーを使うテーブルでは容量を節約でき、検索も速くなることがあります。ただしlast_insert_rowid()やインクリメンタルBLOB I/Oなど一部の機能が使えなくなるので、デフォルトで使うものではなく、目的を持って選ぶ機能だと考えてください。