Oracle も 12c から自動採番ができるようになった

メモ:

MySQL の AUTO_INCREMENT のような機能が Oracle 12c からできるようになりました。 INSERT される都度、 ID 項目などに連番を振っていく機能です。

列の定義は、「GENERATED ALWAYS AS IDENTITY」と「GENERATED BY DEFAULT AS IDENTITY」が指定でき、列の値を更新できるかどうかの違いがあります。

GENERATED ALWAYS AS IDENTITY

次のように「GENERATED ALWAYS AS IDENTITY」を指定したテーブルを作成します。

SQL> CREATE TABLE T_GENERATE_ID (id NUMBER GENERATED ALWAYS AS IDENTITY, name VARCHAR2(32));

表が作成されました。

テーブルが作成されるのですが、実際にはシーケンスも作成されています。

SQL> SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME       OBJECT_TYPE
----------------- ------------------------------------------
T_GENERATE_ID     TABLE
ISEQ$$_23359      SEQUENCE

作成したテーブルの定義を確認すると次のように表示され、列のデフォルト値としてシーケンスが指定されていることが確認できます。

SQL> SELECT column_name, identity_column, data_default FROM user_tab_columns 
WHERE table_name='T_GENERATE_ID' AND column_name='ID';

COLUMN_NAME  IDENTI      DATA_DEFAULT
------------ ----------- -----------------------------------
ID            YES         "ZERO"."ISEQ$$_23359".nextval

次のように INSERT 文でデータを追加すると、自動的に値が設定されるのが確認できます。

SQL> INSERT INTO T_GENERATE_ID (name) VALUES ('bnote');

1行が作成されました。


SQL> SELECT * FROM T_GENERATE_ID;

    ID     NAME
---------- --------------------------------------------------
    1      bnote

GENERATED BY DEFAULT AS IDENTITY

次は、対象の列に「GENERATED BY DEFAULT AS IDENTITY」を指定して T_GENERATE_ID_DEF テーブルを作成します。ちなみに、この指定では自動採番列に対するUPDATEが成功します。

SQL> CREATE TABLE T_GENERATE_ID_DEF (id NUMBER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR2(32));

表が作成されました。

テーブル定義を確認すると、同じように列のデフォルト値としてシーケンスが指定されていることが確認できます。

SQL> SELECT column_name, identity_column, data_default FROM user_tab_columns 
WHERE table_name='T_GENERATE_ID_DEF' AND column_name='ID';

COLUMN_NAME  IDENTI      DATA_DEFAULT
------------ ----------- -----------------------------------
ID            YES         "ZERO"."ISEQ$$_23361".nextval

次のように INSERT 文でデータを追加すると、自動的に値が設定されるのが確認できます。

SQL> INSERT INTO T_GENERATE_ID_DEF (name) VALUES ('bnote');

1行が作成されました。


SQL> SELECT * FROM T_GENERATE_ID_DEF;

    ID     NAME
---------- --------------------------------------------------
    1      bnote

UPDATE の動作を確認してみる

「GENERATED ALWAYS AS IDENTITY」を指定して作成したテーブル T_GENERATE_ID の id 列を更新してみます。

SQL> UPDATE T_GENERATE_ID SET id = 2 WHERE id=1;
UPDATE T_GENERATE_ID SET id = 2 WHERE id=1
                         *
行1でエラーが発生しました。:
ORA-32796: GENERATED ALWAYSで作成されたアイデンティティ列は更新できません

ORA-32796 が出力され更新できないことが確認できました。

次に「GENERATED BY DEFAULT AS IDENTITY」を指定した T_GENERATE_ID_DEF テーブルの id 列を更新してみます。

SQL> UPDATE T_GENERATE_ID_DEF SET id = 2 WHERE id=1;

1行が更新されました。

エラーが返されることなく更新されていることが確認できます。

SQL> SELECT * FROM T_GENERATE_ID_DEF;

   ID NAME
----- ----------------------------------------------------------------
    2 bnote

この状態で、同じように INSERT を実行するとシーケンスの NEXTVAL が実行されるようで、同じ id が設定されてしまいました。

SQL> INSERT INTO T_GENERATE_ID_DEF (name) VALUES ('blue note');

1行が作成されました。

SQL> SELECT * FROM T_GENERATE_ID_DEF;

   ID NAME
----- ----------------------------------------------------------------
    2 bnote
    2 blue note

シーケンスの定義

シーケンスの定義は、通常の定義と変わらないようなので「GENERATED BY DEFAULT AS IDENTITY」で指定した列の更新の仕方によっては値が重複してしまいます。

SQL> SELECT * FROM USER_SEQUENCES;

SEQUENCE_NAME MIN_VALUE  MAX_VALUE  INCREMENT_BY
------------- ---------- ---------- ------------
ISEQ$$_23359  1          1.0000E+28  1 
ISEQ$$_23361  1          1.0000E+28  1

シーケンスの変更は、「 ALTER SEQUENCE 」文ではできないようで「 ALTER TABLE テーブル名 MODIFY 」を使用します。

SQL> ALTER SEQUENCE ISEQ$$_23359 START WITH 5;
ALTER SEQUENCE ISEQ$$_23359 START WITH 5
                            *
行1でエラーが発生しました。:
ORA-02283: 開始順序番号は変更できません。
SQL> ALTER TABLE T_GENERATE_ID_DEF MODIFY (id GENERATED ALWAYS AS IDENTITY (START WITH 5));

表が変更されました。