SQLiteでは、列が INTEGER型 かつ PRIMARY KEY（主キー）の場合に、AutoIncrement を指定をすることができます。
目次
1. AutoIncrementの使用方法
テーブル定義
ID列に AutoIncrement を指定
|
1 2 3 4 |
create table test ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT ); |
データ登録
ID列に1 から 順にインクリメントされた値が登録されます。
|
1 2 3 4 5 |
sqlite> insert into test ( NAME ) values ( 'Aさん' ); sqlite> insert into test ( NAME ) values ( 'Bさん' ); sqlite> select * from test; 1|Aさん 2|Bさん |
2. 注意事項
AutoIncrement を指定できる列（INTEGER型 かつ PRIMARY KEY）は、ROWIDのエイリアスになります。
※ AutoIncrementの指定ある/なしに関わらず、”INTEGER型 かつ PRIMARY KEY”の条件だけで、ROWIDのエイリアスになります。
ROWIDとはテーブル定義の際に指定しなくても、自動で生成される一意の値を保持する列です。
ROWIDのエイリアスになるということは次の関係が成り立ちます。
AutoIncrement列の値 = ROWIDの値
そして、AutoIncrement を指定すると、ROWID の選択アルゴリズムが変わります。
通常のROWID選択アルゴリズム
通常は、現在の最大値に+1 された値が使用されます。
もし、現在の値が ‘9223372036854775807’ の場合は、ROWIDの制約によりこれ以上インクリメントすることができないため、削除したデータなどのROWIDが再利用されます。
そのため、通常のROWIDの選択アルゴリズムでは、ROWIDの再利用ができるため
登録されたデータの順序 != ROWID
という関係になります。
AutoIncrement指定のROWID選択アルゴリズム
AutoIncrement が指定された場合、ROWID は 増加のみ 行います。
データの削除などで、使えるROWIDがあったとしても再利用されないため
登録されたデータの順序 = ROWID
の関係が成り立ちます。
注意しなければいけないのは、再利用されない ということです。
そのため、仮に AutoIncrement を指定した列に、’9223372036854775807′ を登録した場合、それ以上データを追加できないことを意味します。
以下の例では、AutoIncrement あり / なし のケースで ‘9223372036854775807’ を登録後にデータを登録できるか検証した結果です。
例）AutoIncrement 指定あり
テーブル定義
|
1 2 3 4 |
create table test ( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT ); |
データの登録
|
1 2 3 |
sqlite> insert into test ( ID, NAME ) values ( 9223372036854775807 , 'Aさん' ); sqlite> insert into test ( NAME ) values ( 'Bさん' ); Error: database or disk is full |
始めにID列に 9223372036854775807 を登録後、次にID列を指定せずに登録したところエラーなりました。
AutoIncrement で、9223372036854775807 + 1 の値を登録しようとしましたが、ROWIDの制約で登録することが出来ません。
ちなみに、AutoIncrementの自動採番の値では登録出来ませんが、明示的にID列の値を指定すると登録することが出来ます。
|
1 2 3 4 |
sqlite> insert into test ( ID, NAME ) values ( 9223372036854775806 , 'Cさん' ); sqlite> select * from test; 9223372036854775806|Cさん 9223372036854775807|Aさん |
ただ、明示的に指定するプログラムの場合、AutoIncrementは不要だと思われるので、パフォーマンス的にも使用しないことをオススメします。
例）AutoIncrement 指定なし
テーブル定義
|
1 2 3 4 |
create table test2 ( ID INTEGER PRIMARY KEY, NAME TEXT ); |
データの登録
|
1 2 3 4 5 |
sqlite> insert into test2 ( ID, NAME ) values ( 9223372036854775807 , 'Aさん' ); sqlite> insert into test2 ( NAME ) values ( 'Bさん' ); sqlite> select * from test2; 2547917230918658036|Bさん 9223372036854775807|Aさん |
AutoIncrement の指定がないため、ROWIDの最大値（9223372036854775807）に達した場合、未使用整数の中からランダムな値が使用されます。
3. まとめ
SQLiteで AutoIncrement を使用する主な用途は、ROWIDの選択アルゴリズムを増加のみにしたい 場合で、この制約をつける必要がない場合は、AutoIncrement を使用する必要はないと思います。
その理由は
AutoIncrement を使う列は、”INTEGER型 かつ PRIMARY KEY” にする必要がある。
→ “INTEGER型 かつ PRIMARY KEY” にした時点で、ROWIDのエイリアスになる。
→ この状態で主キー列の値を未指定で登録するとROWIDの値が登録される。
と、ROWIDのエイリアスになった時点で、列の値を未指定で登録すると何かしらの値が登録されるため、厳密に値は “一意かつ増加のみ” という場合以外は、AutoIncrement を使用する必要はないと思います。
また、AutoIncrement を使用すると、自動採番で削除されたROWIDが再利用されないため、一度頭打ちになるとそれ以上データが登録出来ないということも起こり得ます。
なので、AutoIncrement を使用する場合は、少々注意する必要があります。