stackoverflow に Q&A があったので、コレを参考にメモ
stackoverflow : create table with sequence.nextval in oracle
http://stackoverflow.com/questions/10613846/create-table-with-sequence-nextval-in-oracle
Oracle 12c以降
Oracle 12c 以降では、ようやくカラムのデフォルト値にシーケンスを指定できるようになる。
将来的には、このアプローチがお手軽。
http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm
1.1.6.1 Default Values for Columns Based on Oracle Sequences
Default values for columns can directly refer to Oracle sequences. Valid entries are sequence.CURVAL and sequence.NEXTVAL.
Providing the functionality to directly refer to a sequence as a default value expression simplifies code development.
CREATE SEQUENCE seq; CREATE TABLE t_seq ( id number default seq.nextval, description varchar2(30) );
やこれのショートカットで
CREATE TABLE t_seq ( id number generated always as identity, description varchar2(30) );
というようなことができるようになる。
詳細は oracle-base の次の記事がよくまとまっている感じ
Identity Columns in Oracle Database 12c Release 1 (12.1)
http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php
Oracle 12c以前
12c 以前ではカラムのデフォルト値にシーケンスを使えない
Restriction on Default Column Values
A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#sthref7262
そこで、シーケンスを用意し、row-level trigger でシーケンスの nextval を設定するのがよく使われる模様。
CREATE SEQUENCE seq; CREATE TABLE t_seq ( id number primary key, description varchar2(30) ); CREATE OR REPLACE TRIGGER t_trigger BEFORE INSERT ON t_seq FOR EACH ROW WHEN (new.id is null) BEGIN SELECT seq.nextval INTO :new.id FROM DUAL; END t_trigger; /
WHEN (new.id is null) の箇所は、明示的に採番するケースがある場合に備えている。
シーケンスでしか採番しないルールであれば不要。
SQL> insert into t_seq(description) values('a');
1 row created.
SQL> insert into t_seq(description) values('b');
1 row created.
SQL> select * from t_seq;
ID DESCRIPTION
---------- ------------------------------
1 a
2 b
sys_guid を使う
「シーケンスで ID 採番」からもう少し話を広げて、「ユニークなIDで採番したい」というように問題設定すると Oracle の独自 uuid である sys_guid が使える
SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions175.htm
生成されたバイト列は、UUID Version 2 (DCE Security)のように、ホスト名情報も含まれているみたいだけど、細かい仕様は不明。
CREATE TABLE t_sysguid(
id raw(16) default sys_guid() primary key,
description varchar2(30)
);
SQL> insert into t_sysguid(description) values('a');
1 row created.
SQL> insert into t_sysguid(description) values('b');
1 row created.
SQL> select * from t_sysguid;
ID DESCRIPTION
-------------------------------- ------------------------------
E54BE8F29B1E6401E040A8C0C80F0B15 a
E54BE8F29B1F6401E040A8C0C80F0B15 b
sequence と sys_guid の pros/cons は次の記事がよくまとまっている感じ
http://rwijk.blogspot.in/2009/12/sysguid.html
ask Tom の次の Q&A も参照
Sankarakumar — Thanks for the question regarding “Using GUIDs as primary keys”, version 11.2.0
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2570145300346198113
PostgreSQL で sequence/uuid を使う
PostgreSQL では SERIAL 型を指定すると、シーケンスの作成とシーケンスのデフォルト値の設定までを裏でやってくれる。
より具体的には
CREATE TABLE tablename (
colname SERIAL
);
は
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
と同じ。
参照
8.1.4. Serial Types
http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
uuid の場合も
CREATE TABLE t_sysguid( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), description varchar2(30) );
というようにすれば OK
参照
8.12. UUID Type
http://www.postgresql.org/docs/9.3/static/datatype-uuid.html
手元のOracleシステム
手元の Oracle を利用したシステム(←何年にもわたって様々な人が増改築を行ってきた)を確認すると、ID 採番ルールは統一化されておらず、かなり混沌としていた。
ざっと以下の4パターンが存在している模様。
- シーケンスの nextval を SELECT し、INSERT 時に指定
- シーケンスの nextval を SELECT し、マスタで利用されていないことを確認した後、INSERT 時に指定(←シーケンス以外の採番も存在しているのか?)
- max(id) を SELECT し、その +1 をINSERT 時に指定
- ID で ORDER BY した SELECT 結果の一番最後のレコードの ID+1 を INSERT 時に指定(戦場だったのか?)
「もっとマシな方法あるんじゃないの?」と今回調べたわけです。
Siguniang's Blog 