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));
表が変更されました。