Oracle Database 自動インクリメント手法メモ

概要

Oracle Database の CREATE TABLE には AUTO INCREMENT な構文が無いので、自前でシーケンス等を使って連番管理する。

今更って話ではありますが主に自分用にメモ。

ケース1: MAX を使う

一番理解が楽。

ただしレコードが既にある場合でないと動かないので注意。(MAX(id) が NULL になるので…)
あと、トランザクションで包まないとタイミングによっては ID が重複するかも。
さらに、レコード削除後の挿入で ID が再利用される可能性もあるので注意。

CREATE TABLE mytable(
   id NUMBER NOT NULL,
   col2 VARCHAR2(20)
);
INSERT INTO mytable(id, col2) VALUES(0, ''); -- ダミーレコード

INSERT INTO mytable(id, col2) SELECT MAX(id) + 1, 'hoge' FROM mytable;
INSERT INTO mytable(id, col2) SELECT MAX(id) + 1, 'piyo' FROM mytable;
INSERT INTO mytable(id, col2) SELECT MAX(id) + 1, 'fuga' FROM mytable;

ケース2: MAX を使う (レコード無い場合も考慮)

NULL が返る可能性のある MAX の戻り値を NVL でラップすると安全。CASE による分岐より楽です。
ID の重複とか再利用される問題はケース1と同じく残っているので注意。

CREATE TABLE mytable(
   id NUMBER NOT NULL,
   col2 VARCHAR2(20)
);

INSERT INTO mytable(id, col2) SELECT NVL(MAX(id), 0) + 1, 'hoge' FROM mytable;
INSERT INTO mytable(id, col2) SELECT NVL(MAX(id), 0) + 1, 'piyo' FROM mytable;
INSERT INTO mytable(id, col2) SELECT NVL(MAX(id), 0) + 1, 'fuga' FROM mytable;

ケース3: SEQUENCE を作って手動で埋め込む

たかだか自動インクリメントのために SEQUENCE という新しい概念を導入するのがなー、ってのがアレだけど安全な手法ではある。

CREATE TABLE mytable(
   id NUMBER NOT NULL,
   col2 VARCHAR2(20)
);

CREATE SEQUENCE myseq;

INSERT INTO mytable(id, col2) VALUES(myseq.NEXTVAL, 'hoge');
INSERT INTO mytable(id, col2) VALUES(myseq.NEXTVAL, 'piyo');
INSERT INTO mytable(id, col2) VALUES(myseq.NEXTVAL, 'fuga');

ケース4: SEQUENCE を作って TRIGGER で埋め込む

一応これで他のデータベースエンジンのように自動インクリメントは可能である。

CREATE TABLE mytable(
   id NUMBER NOT NULL,
   col2 VARCHAR2(20)
);

CREATE SEQUENCE myseq;

CREATE TRIGGER mytrig BEFORE INSERT ON mytable FOR EACH ROW
BEGIN
   SELECT myseq.NEXTVAL
   INTO :new.id
   FROM DUAL;
END;
/

INSERT INTO mytable(col2) VALUES('hoge');
INSERT INTO mytable(col2) VALUES('piyo');
INSERT INTO mytable(col2) VALUES('fuga');