Oracleでデフォルト値にシーケンスを設定したい

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パターンが存在している模様。

  1. シーケンスの nextval を SELECT し、INSERT 時に指定
  2. シーケンスの nextval を SELECT し、マスタで利用されていないことを確認した後、INSERT 時に指定(←シーケンス以外の採番も存在しているのか?)
  3. max(id) を SELECT し、その +1 をINSERT 時に指定
  4. ID で ORDER BY した SELECT 結果の一番最後のレコードの ID+1 を INSERT 時に指定(戦場だったのか?)

「もっとマシな方法あるんじゃないの?」と今回調べたわけです。

Advertisements
Tagged with: , , , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 6 months ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 6 months ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 6 months ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 1 year ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 1 year ago
%d bloggers like this: