自動採番の書き方 - 自動採番の方言 (SERIAL / AUTO_INCREMENT / IDENTITY)

主キーの自動採番は SERIAL / IDENTITY / AUTO_INCREMENT / ROWID と RDBMS ごとに別物。定義方法と挿入直後の ID 取得方法を比較

概念図

自動採番の方言 (SERIAL / AUTO_INCREMENT / IDENTITY) diagram

構文

sql

id SERIAL / id INT AUTO_INCREMENT / id INT IDENTITY(1,1) / id INTEGER PRIMARY KEY

サンプル

各 RDBMS での自動採番主キー定義

sql

-- PostgreSQL (推奨: IDENTITY)
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

-- MySQL
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

-- SQLite
CREATE TABLE users (
  id INTEGER PRIMARY KEY,  -- ROWID のエイリアス。自動採番
  name TEXT
);

-- SQL Server
CREATE TABLE users (
  id INT IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(100)
);

自動採番の方式が分かれている理由

自動採番は「シーケンス（別オブジェクト）方式」と「列属性方式」の 2 つの思想に分かれています。PostgreSQL / Oracle はシーケンス派（SERIAL も裏はシーケンス）、MySQL / SQL Server / SQLite は列属性派です。

SQL:2003 で GENERATED AS IDENTITY が標準化され、PostgreSQL 10 以降や SQL Server はこれを採用しています。新規プロジェクトでは IDENTITY を第一候補にするのが無難です。

RDBMS 別比較表

RDBMS	定義	挿入後の ID 取得
PostgreSQL	`SERIAL` / `BIGSERIAL` / `GENERATED ALWAYS AS IDENTITY`	`INSERT ... RETURNING id` または `currval('seq')`
MySQL	`INT AUTO_INCREMENT`	`LAST_INSERT_ID()`
SQLite	`INTEGER PRIMARY KEY` (ROWID alias) / `AUTOINCREMENT`	`last_insert_rowid()`
SQL Server	`INT IDENTITY(1,1)`	`SCOPE_IDENTITY()` または `OUTPUT INSERTED.id`
Oracle	`GENERATED AS IDENTITY` (12c+) / シーケンス + トリガ	`RETURNING id INTO :var`

SQLite の ROWID と AUTOINCREMENT の違い

SQLite では INTEGER PRIMARY KEY だけで ROWID のエイリアスとなり、自動採番されます。AUTOINCREMENT キーワードを付けると追加でシーケンステーブル sqlite_sequence を使い、削除しても番号を再利用しない挙動になります。

パフォーマンスとディスク使用量を考えると、再利用されても問題ないなら AUTOINCREMENT を付けないのが推奨です。

sql

-- 再利用OK (推奨)
CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);

-- 再利用しない (厳密)
CREATE TABLE b (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

落とし穴

ロールバックでも連番は進む: どの RDBMS でも、INSERT がロールバックされてもシーケンス値は戻らない。ID に穴があく前提で設計する
SQL Server の @@IDENTITY は危険: トリガ内の別テーブル挿入も拾ってしまう。必ず SCOPE_IDENTITY() を使う
MySQL の LAST_INSERT_ID(): 同一接続内で有効。コネクションプールで別接続に戻ると値は消える
PostgreSQL の SERIAL は裏シーケンス: ALTER COLUMN TYPE ではシーケンスは自動リセットされない。大きな値を手動 INSERT した後は setval() で調整が必要
分散環境では連番が向かない: 複数ノード書き込みや移行時は UUID や Snowflake ID を検討