• テーブルに初めて連番が振られたとき、sqlite_sequence にレコードが作成される。
  • sqlite_sequence のレコードは、各テーブルに最後に振られた連番を保持する。
  • sqlite_sequence のレコードを消すと、次の連番は現存するデータの最大値 + 1から再開する。
  • sqlite_sequence の値が巻き戻っても、次の連番が重複しないように調整してくれる。

SQLiteは、AUTOINCREMENTの情報を sqlite_sequence というテーブルで管理してるみたいです。これの挙動を調べてみました。

  1. 基本
  2. sqlite_sequence を消すと
  3. sqlite_sequence を巻き戻すと
  4. sqlite_sequence を進めると

基本

まず、PKが連番のテーブルを適当に作ります。

sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);

この時、まだデータベース内に sqlite_sequence テーブルが存在しなければ作成されます。

sqlite_master を覗いて定義を見てみると、sqlite_sequence テーブルは name と seq という名前のカラムを持ってることが分かります。

-- コマンドラインで見やすくなるように
.headers on
.mode column

-- 確認
sqlite> SELECT * FROM sqlite_master;
type        name        tbl_name    rootpage    sql
----------  ----------  ----------  ----------  -------------------------------------------------------------------------------------
table       my_table    my_table    2           CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL)
table       sqlite_seq  sqlite_seq  3           CREATE TABLE sqlite_sequence(name,seq)

次に、先程作成したテーブル (PKが連番) にレコードを追加します。

-- レコード追加
sqlite> INSERT INTO my_table (code) VALUES ('aaa');
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa

-- sqlite_sequence を確認
sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    1

すると sqlite_sequence に、テーブル名と連番を記録するレコードが追加されました。

ここでは my_table に1を振った状態になっています。sqlite_sequence の seq は、最後にレコードを追加した時に振られた連番です。

以後はレコードを追加すると、この数字が増えていきます。

-- レコード追加
sqlite> INSERT INTO my_table (code) VALUES ('bbb');
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
2           bbb

-- 2になった
sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    2

テーブルを DROP すると、sqlite_sequence の当該テーブルのレコードも消えます。これで sqlite_sequence がカラになった場合は、sqlite_sequence ごと消えるみたいです。

sqlite> DROP TABLE my_table;
sqlite> SELECT * FROM sqlite_sequence;
-- ※結果無し

sqlite_sequence を消すと

意地悪してみます。sqlite_sequence を勝手に DELETE してしまいました。(DROP はエラーになるのでできません。)

-- テーブル作成してデータ追加
sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);
sqlite> INSERT INTO my_table (code) VALUES ('aaa');
sqlite> INSERT INTO my_table (code) VALUES ('bbb');
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
2           bbb

-- sqlite_sequence の当該レコードを削除
sqlite> DELETE FROM sqlite_sequence WHERE name = 'my_table';
sqlite> SELECT * FROM sqlite_sequence;
-- ※結果無し

この状態でさらに my_table にレコードを追加してみます。

-- レコード追加
sqlite> INSERT INTO my_table (code) VALUES ('ccc');
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
2           bbb
3           ccc

-- sqlite_sequence が3で復活
sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    3

すると、自動的に sqlite_sequence が復活しました。値は3です。

復活するときの値は、レコード追加前に my_table に存在した連番の最大値から決定されるみたいです。ここまでの結果に続けて、以下の操作を行うと確認できます。

-- 連番が4となるレコードを追加し、削除する
sqlite> INSERT INTO my_table (code) VALUES ('ddd');
sqlite> DELETE FROM my_table WHERE id = 4;

-- 連番が2のレコードを削除し、歯抜けにする
sqlite> DELETE FROM my_table WHERE id = 2;

-- 現在の状態を確認
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
3           ccc

sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    4

-- sqlite_sequence の当該レコードを削除
sqlite> DELETE FROM sqlite_sequence WHERE name = 'my_table';

-- この状態で my_table にレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('eee');

-- 結果を確認
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
3           ccc
4           eee

sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    4

my_table にかつて存在したはずの4番が居ないので、復活時にもう一度4番が回ってきました。

sqlite_sequence を巻き戻すと

もっと意地悪してみます。今度は sqlite_sequence を勝手に UPDATE して、過去の番号に戻してしまいます。

-- ややこしいのでここまでのテーブルは削除して作り直し
sqlite> DROP TABLE my_table;
sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);

-- 1, 2, 3番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('aaa');
sqlite> INSERT INTO my_table (code) VALUES ('bbb');
sqlite> INSERT INTO my_table (code) VALUES ('ccc');

-- 2番目のレコードを削除して歯抜けにする
sqlite> DELETE FROM my_table WHERE id = 2;

-- sqlite_sequence を 1 でUPDATE
sqlite> UPDATE sqlite_sequence SET seq = 1 WHERE name = 'my_table';

-- 現在の状態を確認
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
3           ccc

sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    1

sqlite_sequence の seq が、実際に存在しているレコードより巻き戻った状態になりました。このまま4番目のレコードを追加してみます。

-- 4番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('ddd');

-- 結果を確認
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
3           ccc
4           ddd

sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    4

追加されたレコードに振られた連番は4でした。ということは、レコード追加時に連番が重複した場合も、テーブルに現存する連番の最大値で自動的に調整されるようです。上述した sqlite_sequence のレコードを消した場合のパターンと同じですね。

sqlite_sequence を進めると

逆のパターンもやっておきます。sqlite_sequence を勝手に UPDATE して、もっと先の番号に進めてしまいます。

-- ややこしいのでここまでのテーブルは削除して作り直し
sqlite> DROP TABLE my_table;
sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);

-- 1番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('aaa');

-- sqlite_sequence を 9 でUPDATE
sqlite> UPDATE sqlite_sequence SET seq = 9 WHERE name = 'my_table';

-- 現在の状態を確認
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa

sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    1

sqlite_sequence の seq が、実際に存在しているレコードより先に進んだ状態になりました。このまま2番目のレコードを追加します。

-- 2番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('bbb');

-- 結果を確認
sqlite> SELECT * FROM my_table;
id          code
----------  ----------
1           aaa
10          bbb

sqlite> SELECT * FROM sqlite_sequence;
name        seq
----------  ----------
my_table    10

追加されたレコードに振られた連番は10でした。先に進める分には、変更した結果がそのまま反映されます。

というか普通に使ってても、物理削除したレコードの連番を勝手に詰められたら困るから、これは当然でしたね。