1. 導入：なぜこの違いを理解する必要があるのか
データベース設計において、テーブルの主キーに自動採番（インクリメント）機能を持たせることは一般的です。しかし、SQLiteを使用する際、安易に「AUTOINCREMENT」を付けていませんか？実は、このオプションを付けるかどうかで、データ削除後のID再利用ルールが大きく変わります。本記事では、この挙動の違いを明確にし、実務でどちらを採用すべきかの判断基準を解説します。
2. 基礎知識：INTEGER PRIMARY KEYとAUTOINCREMENT
SQLiteでは、カラムに「INTEGER PRIMARY KEY」を指定するだけで、レコード追加時に値を指定しなければ自動的に連番が割り当てられます。
一方、そこに「AUTOINCREMENT」を付与すると、内部的な採番ロジックが変更されます。
AUTOINCREMENTなし：現在のテーブル内の「最大値 + 1」を割り当てます。
AUTOINCREMENTあり：過去に割り当てられたことのある「最大値 + 1」を割り当てます。
3. 実装・解決策：挙動の違いを確認する
AUTOINCREMENTなしの場合、最大IDのレコードを削除すると、次に挿入されるデータには削除されたIDが再利用されます。これに対し、AUTOINCREMENTありの場合は、削除されてもそのIDは「過去に使われたもの」としてカウントされるため、再利用されることはありません。
4. サンプルプログラム
以下のSQLを実行することで、両者の挙動の違いを即座に確認できます。
— 準備：AUTOINCREMENTなしのテーブル
CREATE TABLE users_normal (id INTEGER PRIMARY KEY, name TEXT);
— データ挿入
INSERT INTO users_normal (name) VALUES (‘佐藤’), (‘鈴木’), (‘高橋’);
— ここでID 3を削除
DELETE FROM users_normal WHERE id = 3;
— 再度挿入すると、欠番の3が再利用される
INSERT INTO users_normal (name) VALUES (‘田中’);
— 準備：AUTOINCREMENTありのテーブル
CREATE TABLE users_auto (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
— データ挿入
INSERT INTO users_auto (name) VALUES (‘佐藤’), (‘鈴木’), (‘高橋’);
— ここでID 3を削除
DELETE FROM users_auto WHERE id = 3;
— 再度挿入すると、過去の最大値3を考慮し、4が採番される
INSERT INTO users_auto (name) VALUES (‘田中’);
— 応用：現在の最大採番値を確認する
— AUTOINCREMENT設定時は、SQLite内部の管理テーブルを参照可能です
SELECT FROM sqlite_sequence WHERE name = ‘users_auto’;
5. 応用・注意点：現場での判断基準
実務においてAUTOINCREMENTを付けるべきか迷った際は、以下の観点を考慮してください。
AUTOINCREMENTを避けるべき理由：
SQLite公式ドキュメントでも言及されていますが、AUTOINCREMENTを付与すると、内部で「sqlite_sequence」テーブルを更新するオーバーヘッドが発生し、パフォーマンスがわずかに低下します。また、IDが連番であることや、再利用されないことにビジネスロジック上の強い必然性がない限り、基本的には不要です。
AUTOINCREMENTが必要なケース：
外部システムとIDを連携しており、一度発行したIDが二度と出現してはならない（重複を厳密に避ける必要がある）場合や、ログの時系列をIDの昇順で厳密に管理したい場合には有用です。
注意点：
「IDが欠番になるのが気持ち悪い」という理由だけでAUTOINCREMENTを採用するのは避けましょう。データベースの主キーは、あくまで「レコードを一意に識別するためのもの」であり、連番である必要は必ずしもありません。システムの要件を再確認し、必要最小限の機能で設計することを推奨します。
コメント