AUTOINCREMENTを設定する場合としない場合の違い

SQLite の環境で INTEGER PRIMARY KEY に AUTOINCREMENT を合わせて設定した場合にどのように自動的に値が割り当てられるようになるのかについて解説します。また今までに割り当てられたことのある最大の値を確認する方法を合わせてご紹介します。

(Last modified: )

AUTOINCREMENTを設定した場合の値の割り当てルール

カラムに対して INTEGER PRIMARY KEY を設定した場合、データを追加した時に INTEGER PRIMARY KEY を設定したカラムの値を指定しないと自動的に値が格納されます。自動的に格納される値は、対象のカラムに格納されている最大の値に 1 を加えた値となります。この値は以前に割り当てられたことがあるかどうか関係がないため、データの追加と削除を繰り返していると以前に格納されたことがある値が再度カラムに格納される場合があります。

カラムに INTEGER PRIMARY KEY を設定する時に AUTOINCREMENT を合わせて設定すると自動的に設定される値のルールが変わります。対象のカラムに現在格納されている最大の値に 1 が加えられるのではなく、対象のカラムに今までに格納されたことのある最大の値に 1 が加えられた値が格納されるようになります。

INTEGER PRIMARY KEY に AUTOINCREMENT を合わせて設定するには次のように記述します。

CREATE TABLE テーブル名(カラム名 INTEGER PRIMARY KEY AUTOINCREMENT, ...);

データが残っているかどうかは関係がないため、自動的に設定される値は常に今までで最大の値となります。その結果、今までに格納されたことがある値が自動的に格納されることはありません。

では実際に試してみます。まずは AUTOINCREMENT を指定しない場合にどうなるのかを確認してみます。次のようなテーブルを作成しました。

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

AUTOINCREMENTを設定した場合の値の割り当てルール(1)

3 つのデータを追加します。 INTEGER PRIMARY KEY を設定してあるカラムには値を指定していませんので自動的に値が格納されます。

insert into user(name) values('Harada');
insert into user(name) values('Yamada');
insert into user(name) values('Tani');

AUTOINCREMENTを設定した場合の値の割り当てルール(2)

テーブルからデータを取得します。

select * from user;

AUTOINCREMENTを設定した場合の値の割り当てルール(3)

ID カラムに一番大きい値が入っているデータ( ID カラムの値が 3 のデータ)を削除します。

delete from user where id = 3;

AUTOINCREMENTを設定した場合の値の割り当てルール(4)

ここで再びデータを追加します。この時 INTEGER PRIMARY KEYを 設定してあるカラムに値を指定しない場合、対象のカラムに格納されている最大の値(現在は 2 )に 1 を加えた値が自動的に設定されるため、カラムに自動的に設定される値は 3 となります。

insert into user(name) values('Ueda');

AUTOINCREMENTを設定した場合の値の割り当てルール(4)

このように以前に一度別のデータに割り当てられた値であっても関係無く最大の値に 1 を加えた値がカラムに設定されます。

----

では次に AUTOINCREMENT を合わせて指定してみます。次のようなテーブルを作成しました。

create table user(id integer primary key autoincrement, name text);

AUTOINCREMENTを設定した場合の値の割り当てルール(6)

先ほどと同じように3つのデータを追加します。 INTEGER PRIMARY KEY AUTOINCREMENT を設定してあるカラムには値を指定していませんので自動的に値が格納されます。

insert into user(name) values('Harada');
insert into user(name) values('Yamada');
insert into user(name) values('Tani');

AUTOINCREMENTを設定した場合の値の割り当てルール(7)

ID カラムに一番大きい値が入っているデータ( ID カラムの値が 3 のデータ)を削除します。

delete from user where id = 3;

AUTOINCREMENTを設定した場合の値の割り当てルール(8)

ここで再びデータを追加します。この時 INTEGER PRIMARY KEY AUTOINCREMENT を設定してあるカラムに値を指定しない場合、対象のカラムに今までに格納されたことのある最大の値(現在は 3 )に 1 を加えた値が自動的に設定されるため 4 が格納されます。

insert into user(name) values('Ueda');

AUTOINCREMENTを設定した場合の値の割り当てルール(9)

このように AUTOINCREMENT を設定すれば以前に設定されたことのある値が再び使用されることを防ぐことが可能です。

カラムに格納されたことのある最大の値

AUTOINCREMENT がカラムに設定されている場合、削除されたデータも含めて過去に対象のカラムに格納されたことのある最大の値を参照することになりますが、この値は SQLite が自動で作成する特別なテーブルである sqlite_sequence テーブルに格納されています。

sqlite_sequence テーブルのスキーマを確認してみると、 name と seq の2つのカラムが含まれていることが分かります。

.schema sqlite_sequence

カラムに格納されたことのある最大の値(1)

name にはテーブル名、 seq には INTEGER PRIMARY KEY AUTOINCREMENT が設定されたカラムに過去に割り当てられたことのある最大の値が格納されます。では先ほど使用していた user テーブルの最大の値を確認してみます。

select * from sqlite_sequence where name = 'user';

カラムに格納されたことのある最大の値(2)

現在 user2 テーブルでは INTEGER PRIMARY KEY AUTOINCREMENT が設定されたカラムに今までに格納された最大の値が 4 であることが確認できます。

-- --

SQLite の環境で INTEGER PRIMARY KEY に AUTOINCREMENT を合わせて設定した場合にどのように自動的に値が割り当てられるようになるのかについて解説しました。

( Written by Tatsuo Ikura )

プロフィール画像

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。