[Oracle12c]auto incrementみたいな新機能

Oracle12cでは、auto incrementっぽいカラムを定義できるようなので試してみた。

auto incrementっぽいカラムを定義するには、下のSQLのように「generated as identity」を指定する。

-- 常に生成した値を設定する（明示的に値を設定しようとするとエラーとなる)
id number generated as identity not null

-- generatedの後にalwaysを指定すると、常に生成した値を使用する
-- デフォルト動作なので、上のオプション無しと同じ動きとなる
id number generated always as identity not null

-- by defaultを指定すると値の設定がない場合のみ、生成した値が設定されます。
-- on nullをby defaultの後に設定すると、挿入（更新）対象の値がnullの場合にも、生成した値が設定されます。
id number generated by default as identity not null

生成する値のルールは、オプションとして指定します。指定する値のルールはシーケンスオブジェクトと同じなので省略します。

例

初期値が100で、最大値が999でサイクルさせる場合の定義例

id number generated as identity(
  minvalue 100
  maxvalue 999
  cycle
  ) not null,

実行例

-- テーブル定義
create table test_table
(
  id number generated as identity(
  minvalue 100
  maxvalue 999
  cycle
  ) not null,
  name NVARCHAR2 (100) not null,
  primary key (id)
);

-- INSERTの実行結果
SQL> insert into test_table (name) values ('なまえ');

1 row created.

SQL> select id from test_table;

 ID
----------
       100

SQL> insert into test_table (name) values ('なまえ');

1 row created.

SQL> select id from test_table;

 ID
----------
       100
       101

always指定のカラムに対して値を設定しようとすると、以下の例外が発生する。

SQL> insert into test_table (id, name) values (999, 'name');
insert into test_table (id, name) values (999, 'name')
                        *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

値ってどうやって生成している？

値の生成自体はテーブル作成時に裏で作成したシーケンスオブジェクトを使用して行っているようです。
このシーケンスオブジェクトは、単純に「drop table テーブル名」でテーブルを削除した場合ゴミとして残ってしまいます。
テーブル削除時に「purge」を指定してセットで削除してあげましょう。