😺

【SQL入門】採番とシーケンス

に公開
SQL
初心者
シーケンス
採番
tech

採番

テーブルに新しいレコード（行）を追加する際に、自動的に一意な番号（ID）を割り当てる機能のことです。この番号は、主にそのレコードを識別するための主キーとして使われます。

採番テーブル

複数のシステムやサービス間で一意な番号（ID）を生成・共有するために使われる特別なテーブルです。
データベースの自動採番機能（AUTO_INCREMENTなど）だけでは対応できない複雑な要件を満たすために利用されます。

採番テーブルの仕組み

列名 データ型 説明
id_name VARCHAR 採番対象のIDを識別するための名前（例：order_id, user_id）
current_id BIGINT 次に割り当てる番号

アプリケーションは、新しいIDが必要になったとき、以下のステップでこのテーブルを利用します。

  1. ロック: 複数のプロセスが同時にアクセスしないよう、該当する行をロックします
  2. 取得: current_id の値を取得します。これが新しいIDになります
  3. 更新: current_id の値をインクリメントして更新します
  4. 解放: ロックを解放します

この一連の処理をトランザクションとして実行することで、IDの重複を防ぎ、一意性を保証します。

採番テーブルが適しているケース

  • 複数のテーブルで同じIDを共有する場合
  • IDに特定のルールを持たせる場合
  • データベースを分散させる場合

採番の仕組み

DBMS（データベース管理システム）は、採番のための特別なデータ型やプロパティを提供しています。

  • AUTO_INCREMENT (MySQL, SQLite)
    CREATE TABLE文で列にこのキーワードを付与すると、新しいレコードが挿入されるたびに、自動的に1ずつ増える整数が割り当てられます。

  • IDENTITY (SQL Server)
    MySQLの AUTO_INCREMENT と同様の機能を提供します。

  • SERIAL (PostgreSQL)
    整数型の一種で、自動的に一意な番号を生成します。

これらの機能を使うことで、開発者が手動でIDを管理する手間が省け、データの重複や一意性に関する問題を回避できます。

シーケンス（sequence）

データベース内で一意な連続した整数を自動的に生成するためのオブジェクトです。
採番テーブルと同様に、複数のテーブルで一貫したユニークなIDを割り当てる目的で使われます。

シーケンスの仕組み

シーケンスは、CREATE SEQUENCEというDDL（データ定義言語）コマンドで作成されます。
このオブジェクトは、以下のような情報を持ちます。

  • 開始値（START WITH）: 最初に生成される番号
  • 増分値（INCREMENT BY）: 番号が1回生成されるごとに増える値（通常は1）
  • 最大値（MAXVALUE）: 生成される番号の最大値
  • キャッシュ（CACHE）: パフォーマンス向上のために、DBMSがメモリ上に事前に確保しておく番号の数

アプリケーションは、NEXTVAL という専用の関数を使ってシーケンスから次の番号を取得します。
これにより、複数のセッションが同時に番号を要求しても、重複することなく一意な番号を安全に取得できます。

シーケンスは常に採番した最新の値を記録しており、シーケンスに指示をすることで「現在の値」や「次の値」を取り出すことができる

ただし、シーケンスから値を取り出すと、その操作はすぐに確定し、トランザクションをロールバックしてもシーケンスの値は戻らない点に注意。
これは、1つのシーケンスが複数のトランザクションから利用されることを想定しているため

シーケンスがオブジェクトであることの利点

  1. 状態の保持
    シーケンスは、現在の値（CURRVAL）や次に割り当てるべき値（NEXTVAL）といった状態を内部に保持しています。これは、シーケンスが単なる関数ではなく、状態を持つオブジェクトとして設計されているからです。

  2. 独立した管理
    シーケンスは特定のテーブルに紐づいていません。
    これにより、複数のテーブルや、データベース外のアプリケーションからでも、同じシーケンスオブジェクトにアクセスして一意な番号を安全に取得できます。
    これにより、番号の生成ロジックが一元化され、管理が容易になります。

  3. アトミックな操作
    シーケンスからの番号取得は、データベースシステムによってアトミック（不可分）に実行されます。
    つまり、複数のプロセスが同時に「次の値」を要求しても、内部のカウンタは一度に1つずつ正確に増加し、番号が重複することはありません。これは、シーケンスがデータベースのトランザクション管理に組み込まれているためです。

自動採番機能との違い

AUTO_INCREMENT（MySQLなど）やIDENTITY（SQL Server）といった自動採番機能は、特定のテーブルの1つの列に紐づいていますが、シーケンスはテーブルから独立したデータベースオブジェクトです。この独立性が、シーケンスの最大の利点です。

  • 複数のテーブルでの利用: 1つのシーケンスから生成された番号を、複数の異なるテーブルの主キーとして利用できます

  • データの型: シーケンスは、数値IDだけでなく、複合的なIDの一部として使われることもあります（例：'ORD-' || nextval('order_seq')）

シーケンスの作成
CREATE SEQUENCE シーケンス名
シーケンスの削除
DROP SEQUENCE シーケンス名

DBMSによって作成後の使い方が異なるので、各DBMSのマニュアル参照

参考図書

『スッキリわかるSQL入門 第2版』

Discussion