既存のレコードの番号の最大値＋１なんていうやり方もありますが、このようなやり方では同時実行性に問題があります。
また、採番テーブルを使うような運用もよく見かけますが、これもやはり同時実行性に気をつけないといけないし、仕組み的にも複雑になります。
このような問題に対処するためか、多くのRDBMSには一意な番号を自動採番する機能が提供されています。
ただし、その仕組みはRDBMSのよって異なります。
SQL Server
SQL Serverの場合、数値型のデータ型のカラムにIDENTITY属性を付与することにより、一意な番号の自動採番が可能になります。
例えば、以下のようにIDENTITY属性を指定した数値型のカラムを含むテーブル作成し、
CREATE TABLE TABLE1 ( ID int IDENTITY (1, 1) NOT NULL , NAME nvarchar (100) NOT NULL ) ※IDENTITY属性の後ろのカッコ内数値は、左が開始値、右が増分を表します。
IDカラムに値をセットせずにレコードをINSERTすると、
INSERT INTO TABLE1 ( NAME ) VALUES ( 'name1' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name2' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name3' )
以下のようにIDが自動採番されてレコードが登録されます。
ID NAME ----------- 1 name1 2 name2 3 name3
と、何も意識せずにテーブルにレコードをINSERTするだけで勝手に番号が自動採番されるという非常に簡単な仕組みになっています。
ただし、上記のINSERT文を見てもわかるように、IDENTITY属性を指定したカラムはINTOカラムリストにもVALUESリストにもセットしていません。
IDENTITY属性を指定したカラムに明示的に値をセットするとエラーになるからです。
ゆえに、INSERT INTO TABLE1 [ VALUES | SELECT ] ～というようにINTOカラムリストを省略したINSERTはできないので注意が必要です。
※SET IDENTITY_INSERT ON テーブル名とすれば明示的な値のセットも可能ですが、基本的には使用しない方がよい。
また、数値型カラムにIDENTITY属性を指定するという形での実装であるため、ただ単に番号だけを自動採番したいというようなときにもわざわざテーブルを定義してレコードをINSERTしなければなりません。
このような用途で使用する場合はちょっとブサイクな実装になってしまうのが欠点です。
Oracle
Oracleの場合、SQL Serverのようにカラムの属性の指定によって一意な番号を自動採番することはできません。
同じことを実現するには順序（SEQUENCE）オブジェクトを使用します。
順序は以下のように作成します。
CREATE SEQUENCE ID_SEQ;
この例では、オプションを何も設定していないので、初期値1で1ずつインクリメントする順序となります。
指定できる主なオプションは以下のとおりです。
| START WITH n | 初期値をnとする。デフォルトは1。 |
| MAXVALUE n | 最大値をnとする。デフォルトは昇順の場合10の27乗、降順の場合-1。 |
| MINVALUE n | 最小値をnとする。デフォルトは昇順の場合1、降順の場合-10の26乗。 |
| INCREMENT BY n | 増分値をnとする。デフォルトは1。 |
| CYCLE | NOCYCLE | CYCLEを指定すると最大値に達した場合は最小値に戻って、最小値に達した場合は最大値に戻って値の生成を続ける。デフォルトはNOCYCLE。 |
| CACHE n | NOCACHE | CACHEを指定すると値の生成を高速に行うためにあらかじめn個の値を生成しメモリ上にキャッシュしておく。デフォルトはCACHE 20。 |
順序から一意な番号を取得するにはNEXTVAL擬似列を使用します。
SELECT ID_SEQ.NEXTVAL FROM DUAL;
また、現在の番号を取得するにはCURRVAL擬似列を使用します。
SELECT ID_SEQ.CURRVAL FROM DUAL;
このように順序はSQL ServerのIDENTITY属性と違ってカラムと直接結びつくものではないため、様々な場面で利用できます。
その辺は利点といえば利点なのですが、SQL ServerのIDENTITY属性と同じような動作をさせようと思うとちょっと工夫が必要になります。
つまり、テーブルにレコードをINSERTするだけで列に勝手に一意な番号が自動採番されるというような仕組みを作ることですが、これには以下のようなトリガーを作成する必要があります。
CREATE OR REPLACE TRIGGER TABLE1_CREATE_ID_TRIGGER
BEFORE INSERT
ON TABLE1
FOR EACH ROW
BEGIN
IF :NEW.ID IS NULL THEN
SELECT ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END IF;
END;
このトリガーはTABLE1へのレコードのインサート前に実行され、ID列がNULLだったら順序（ID_SEQ）から新しい番号を取得してセットしています。
つまり、SQL Serverと同様に、以下のようなINSERT文を実行すると
INSERT INTO TABLE1 ( NAME ) VALUES ( 'name1' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name2' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name3' )
以下のようにIDが自動採番されてレコードが登録されるというわけです。
ID NAME ----------- 1 name1 2 name2 3 name3
このトリガーを利用する方法の場合、SQL ServerのIDENTITY属性と違って、列に明示的に値を指定してINSERTすることも可能です。
ただし、順序と値の同期がとれなくなる危険性があるので、このような操作はやらないほうがいいでしょう。
SQL ServerにしてもOracleにしても連番の自動採番は比較的簡単に実装できるのですが、最後に欠番発生のリスクについて触れておきます。
上記の連番の自動採番の仕組みでは、使い方によっては欠番が発生することがあります。
例えば、先程の例のようにテーブルにレコードをINSERTすると、
INSERT INTO TABLE1 ( NAME ) VALUES ( 'name1' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name2' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name3' )
以下のようなレコードが登録されるわけですが、
ID NAME ----------- 1 name1 2 name2 3 name3
上記のINSERT文の後続の処理でエラーが発生するなどしたために、上記のINSERT文をロールバックをしたとすると、レコードは元に戻されるが、そのレコードのために採番された番号はそのままなのです。
つまり、再度同じようにレコードをINSERTすると、
INSERT INTO TABLE1 ( NAME ) VALUES ( 'name1' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name2' ) INSERT INTO TABLE1 ( NAME ) VALUES ( 'name3' )
続きの番号が採番されるのです。
ID NAME ----------- 4 name1 5 name2 6 name3
ということがあるので、欠番の発生を気にするシステムであるならば、別の方法を考えなければなりません。
また、Oracleの場合には、順序の作成時にCACHEを設定していると、Oracleの再起動時などにCACHEに残っていてまだ採番されていない番号がごっそり欠番となってしまいます。
例えば
CREATE SEQUENCE ID_SEQ CACHE 100
と設定していて、そのうち実際に採番されたのが1～50という状態でOracleを再起動すると、次に採番されるのは101からとなってしまうのです。
このCACHEの設定による欠番の発生を気にするシステムであるならば、NOCACHEの設定に変更したほうがよいでしょう。