sqlite と ROWID

sqlite

SQLite を使っていて、主キーがないテーブルで一意にデータを識別する方法がないかと思っていろいろと調べてみました。

Oracle や PostgreSQL で言うところの rowid や oid にあたるものがあれば一撃だなと思ってマニュアルを当たってみたところ、SQLite にも同じようなものが用意されていました。参照するときの名前は rowid でも oid でもどちらでも良いみたいです。下記によると 64bit 長の符号付き整数として自動的に定義されるようですが、負数になるかどうかというのはちょっと謎です。

ROWIDs and the INTEGER PRIMARY KEY - SQLite Query Language: CREATE TABLE

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

以下、動作確認メモ。

まず、簡単なテーブルを作って、データを入れておきます。
下記のような感じでわざと同じデータを２度挿入すると２つの行は一見区別できない状態になります。

sqlite> CREATE TABLE foo ( f1 VARCHAR(20), f2 VARCHAR(20) );
sqlite> insert into foo values( 'hoge', 'fuga' );
sqlite> insert into foo values( 'hoge', 'fuga' );
sqlite> SELECT * FROM foo;
f1          f2
----------  ----------
hoge        fuga
hoge        fuga

rowid を知るためには * ではダメで、明示的にカラム名を指定してやる必要があります。
これで、２つの行を区別することができるようになりました。また、rowid は WHERE の条件として使うこともできます。

sqlite> SELECT rowid,* FROM foo;
rowid       f1          f2
----------  ----------  ----------
1           hoge        fuga
2           hoge        fuga
sqlite> SELECT rowid,* FROM foo WHERE rowid = 2;
rowid       f1          f2
----------  ----------  ----------
2           hoge        fuga

こういうパターンはあまりないかもしれませんが、最後に挿入された行を知るために Last Insert Rowid を使うときなんかにお世話になるかもしれません。