SQLite3 rowid を理解する

概要

SQLite のテーブルの各レコードにはデフォルトで隠しカラムとしてrowidが設定される。
積極的に使うわけではないけど覚えておくと便利な機能、といった程度の認識でいい。
仕様を理解していないうちに頼ってしまうと後々痛い目に合うので注意。

このrowidには符号付64ビットのユニークな整数が自動的に格納される。
役割としてはprimary keyと同じで、テーブルのレコードを一意に識別するために使われる。

この記事ではprimary key や autoincrement を設定していないシンプルなテーブルでの挙動について記載する。

実行例

環境

• Windows 11
• SQLite3 (3.53.1) Command-Line Shell

適当なテーブルを作成し、適当なデータを挿入し、rowidを確認してみる。

rowidに入っている値を調べるには、以下のいずれかのカラム名を指定してselect文を実行する。

• rowid
• oid
• _rowid_

1. アスタリスク（*）では rowidselect されない。
2. 明示的にrowidを指定すると select することができる。
3. rowid、 oid、 _rowid_はすべて同じ値を指し、 カラム名はrowidとして返される。

sqlite> create table user (id, name);
sqlite> insert into user (id, name) values (111, 'sample_user_1');
sqlite> insert into user (id, name) values (222, 'sample_user_2');
sqlite> .mode box

sqlite> -- 1.
sqlite> select * from user;
┌─────┬───────────────┐
│ id  │     name      │
├─────┼───────────────┤
│ 111 │ sample_user_1 │
│ 222 │ sample_user_2 │
└─────┴───────────────┘

sqlite> -- 2.
sqlite> select rowid, * from user;
┌───────┬─────┬───────────────┐
│ rowid │ id  │     name      │
├───────┼─────┼───────────────┤
│ 1     │ 111 │ sample_user_1 │
│ 2     │ 222 │ sample_user_2 │
└───────┴─────┴───────────────┘

sqlite> -- 3.
sqlite> select rowid, oid, _rowid_ from user;
┌───────┬───────┬───────┐
│ rowid │ rowid │ rowid │
├───────┼───────┼───────┤
│ 1     │ 1     │ 1     │
│ 2     │ 2     │ 2     │
└───────┴───────┴───────┘
                

要するに上記のSQLでは、実際には以下のようなテーブルが作成されていることになる。

rowid 別名：oid 別名：_rowid_ （隠しカラム）	id	name
1	111	sample_user_1
2	222	sample_user_2

そのテーブルの最大 rowid + 1 が採番される

例外はあるものの、基本的にはそのテーブルの最大 rowid + 1 が採番される。
そのため、rowid は連番であることが多い。

参考URL

• ROWIDs and the INTEGER PRIMARY KEY(英語)
  公式のROWIDに関するドキュメント
  https://www.sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key
• Rowid Tables（英語）
  Rowid が設定されたテーブルに関するドキュメント
  https://sqlite.org/rowidtable.html

SQLite3 テーブルに主キーを設定する(PRIMARY KEY)

概要

SQLite3 でカラムに主キーを設定するにはいくつか方法があるので この記事ではその方法について記載する。

環境

• Windows 11
• SQLite3 (3.53.1) Command-Line Shell

テーブル作成時にカラムに続けて主キーを指定する

サンプルのテーブルとしてuserテーブルを作成、 id を主キーとする。

「user」テーブル

カラム名	型	制約
id	text	主キー not null制約
name	text

このテーブルを作成するためには以下のSQLを実行する。
カラム名の後にPRIMARY KEYと記述することで、そのカラムに主キーを設定出来る。

create table user (
    id     text  primary key not null
    , name text
);
                

上記のSQLを実行すると、idカラムに主キーが設定される。
そのため、idカラムには重複する値を登録することは出来なくなる。

以下は、上記のテーブルにデータを登録する例。 idカラムに重複する値を登録しようするとエラーになる。

sqlite> insert into user (id, name) values ('001', 'sample_name_1');
sqlite> insert into user (id, name) values ('002', 'sample_name_2');
sqlite> insert into user (id, name) values ('001', 'sample_name_3');
Runtime error: UNIQUE constraint failed: user.id (19)                 
                

補足

上記の方法で複数カラムを主キーとする、いわゆる「複合キー」を設定しようとするとエラーになる。 複合キーを設定するには次項で説明する「テーブル作成時にPRIMARY KEY句を使用する」を参照のこと。

以下は、複合キーを設定しようとしてエラーになる例。

sqlite> create table user (
(x1...>     id     text primary key not null
(x1...>     , name text primary key
(x1...> );
Parse error: table "user" has more than one primary key
                    

テーブル作成時にPRIMARY KEY句を使用する

カラム名に続けて主キーを指定するのではなく、テーブル作成時にPRIMARY KEY句を使用して主キーを設定することも出来る。
この方法を使用することで、複数カラムを主キーとする「複合キー」を設定することが出来る。

以下は、PRIMARY KEY句を使用して主キーを設定する例。

1. idカラムを主キーとする場合
2. idカラムとnameカラムを複合キーとして主キーに設定する場合

-- 1.
create table user (
  id     text not null
  , name text
  , primary key (id)
);

-- 2.
create table user (
  id     text not null
  , name text not null
  , primary key ( id, name )
);
                

複合キーは複数のカラムの値の組み合わせでユニークになることを保証するため、 複合キーに設定したカラムのいずれかに重複する値があっても、他のカラムの値が異なれば登録することが出来る。

以下は、複合キーを設定したテーブルにデータを登録する例。

sqlite> create table user (
(x1...>   id text  not null
(x1...>   , name text not null
(x1...>   , primary key ( id, name )
(x1...> );
sqlite> insert into user (id, name) values ('001', 'sample_name_1');
sqlite> insert into user (id, name) values ('001', 'sample_name_2');
sqlite> insert into user (id, name) values ('001', 'sample_name_1');
Runtime error: UNIQUE constraint failed: user.id, user.name (19)
                

テーブル作成後に主キーを設定する

SQLite3 では、既存のテーブルに対して主キーを ALTER TABLE等で追加設定することは出来ない。 そのため、既存のテーブルに主キーを設定するには、以下の手順で行う必要がある。

1. 新しいテーブルを作成する
2. 既存のテーブルから新しいテーブルにデータをコピーする
3. 既存のテーブルを削除する

詳しい作業手順は下記の過去記事と同じ手順になる。
結構手間がかかるので、最初のテーブル設計はきっちりやるべし。

• SQLite3 カラムの型を変更する

主キーを設定するカラムには必ず NOT NULL 制約を設定する

SQLite3 では PRIMARY KEYを指定しただけでは NULLを入力することができてしまう。

以下はidカラムに主キーを設定したテーブルにNULLを登録する例。

「user」テーブル

カラム名	型	制約
id	text	主キー
name	text

sqlite> .mode box
sqlite> .nullvalue [null]
sqlite> create table user (
(x1...>   id     text primary key
(x1...>   , name text
(x1...> );
sqlite> insert into user (id, name) values (NULL, 'sample_name_1');
sqlite> insert into user (id, name) values (null, 'sample_name_2');
sqlite> select * from user;
┌────────┬───────────────┐
│   id   │     name      │
├────────┼───────────────┤
│ [null] │ sample_name_1 │
│ [null] │ sample_name_2 │
└────────┴───────────────┘
                

• SQLite 3 コマンドラインツールでselectの結果を見やすくする
• SQLite 3 コマンドラインツールでnullの表示を見やすくする

上記の例では、idカラムに主キーを設定しているにも関わらず、 NULLを登録することが出来てしまっている。 さらに、NULLであれば複数登録することも出来てしまっている。
そのため、主キーを設定するカラムにはNOT NULL制約を設定したほうがよい。

これは過去バージョンで主キーにNULLが登録出来てしまっていたことに起因している。 SQLite は後方互換性を重視しているため修正はせず、NULLが登録できる状態を維持する方針らしい。

また、上記の例では主キーのデータ型がtextのためこのような挙動になっているが、 integer型の主キーの場合、もうちょっと厄介な挙動をする。
そのため、主キーを設定するカラムにはNOT NULL制約は必須という考えでいたほうが安全。

参考URL

• PRIMARY KEY(英語)
  公式のPRIMARY KEYに関するドキュメント
  https://www.sqlite.org/lang_createtable.html#the_primary_key
• NOT NULL constraints(英語)
  公式のNOT NULL制約に関するドキュメント
  https://www.sqlite.org/lang_createtable.html#not_null_constraints
• column-constraint(英語)
  公式のカラムに設定する各種制約に関するドキュメント
  https://www.sqlite.org/syntax/column-constraint.html

• SQLite3 備忘録：SQLite3 nullを登録できないようにカラムを設定する(NOT NULL制約)
  https://sfnovicenotes.blogspot.com/2022/04/sqlite3-nullnot-null.html
• SQLite3 備忘録：SQLite3 カラムの型を変更する
  https://sfnovicenotes.blogspot.com/2021/01/sqlite3.html