【データベース設計】採番について

テーブルには、そのテーブルで必ず一意となるように、主キーを設定するかと思います。

主キーは、基本的には、連番になるように設計することが普通です。

とすると、最後に使用した番号をどこかに記録しておかなければなりません。

主キーの採番はいくつか方法があります。

方法１：採番テーブルを用意する。

その際は、採番用の「採番テーブル」を使うことが普通です。

採番テーブルの例

項目	最終発行番号
ユーザーID	10456
端末ID	14987
備品ID	8765

最大のメリットとしては、全てのDBMSで同じように使えることができる汎用性でしょう。

ただ、やはり少し面倒なのがデメリットになります。

方法２：連番採番機能を使う。

この方法は、設計や実装が非常に楽です。

しかし、全てのDBMSで使えるわけでもありませんし、DBMS製品によって利用方法が異なるので、あまり推奨はされないかもしれません。

機能の中でも、DBMSごとにいくつか方法があります。

列定義を使う方法

DBMS	やり方
SQL Server	IDENTITY修飾を付与する。
MySQL	AUTO_INCREMENT修飾を付与する。
PostgreSQL	データ型にSERIAL型を利用する。
SQLite	AUTOINCREMENT修飾を付与する。

シーケンスを使う方法

Oracleや、SQL Server等のDBで対応していますが、「シーケンス」という機能を使う方法です。

シーケンスの特徴

• 常に採番した最新の値を記録しており、シーケンスに指示をすることで、「現在の値」や「最新の値」を自由に取り出すことが可能
• シーケンスから取り出した値は、即時コミット（複数トランザクションからの利用を想定しているため。）

シーケンスの構文(Oracleの場合)

シーケンスを作成する。

CREATE SEQUENCE シーケンス名;

現在の値を得る。

SELECT シーケンス名.CURRVAL FROM DUAL;

次の値を得る。

SELECT シーケンス名.CURRVAL FROM DUAL;

その他方法

あまり多くはないですが、INTEGER型でかつ主キーがついた項目にNULLを挿入すると自動的に連番を振ってくれるDBもあります。

採番において、あまりおすすめできない方法

例えば、下記のように、MAX関数を使って、最大値を取得して採番するという方法を思いつかれた方もいらっしゃるかもしれません。

SELECT MAX(USER_ID) + 1 AS MAX FROM USER_MASTER;

この方法は、結論から申しますと、おすすめできません。

理由

• ロックを使わないと複数の人から、同じ番号が採番されてしまう可能性がある点
• 最後に採番した行が削除されてしまうと、また同じ番号を使うことになる点

大きな理由は一点目の理由でしょう。できるだけ、採番テーブルや、シーケンスを使って採番の仕組みを設計するようにしましょう。