Oracle Database 12c の自動採番列を試す

自動採番列

RDBMSのテーブルに自動的に値を設定する列を作りたいことがあります。Oracle Database以外のRDBMSでは一般的に自動採番列をサポートしています。

RDBMS	構文	構文の説明
DB2	GENERATED ALWAYS AS IDENTITY / GENERATED BY DEFAULT AS IDENTITY	列属性
SQL Server	IDENTITY	列属性
MySQL	AUTO_INCREMENT	列属性
PostgreSQL	serial / bigserial / smallserial, GENERATED ALWAYS AS IDENTITY / GENERATED BY DEFAULT AS IDENTITY	データ型 / 列属性

Oracle Database 12c (12.1)からGENERATED AS IDENTITY属性を指定することで、自動採番列を作成できるようになりました。この構文はSQL標準に準拠しているため、DB2やPostgreSQLと同一になっています。

実行例

実際に作成して確認します。

GENERATED ALWAYS AS IDENTITY

自動採番された値は更新できません。自動採番列に対するUPDATE文は失敗します。
下記はNUMBER型のid列を自動生成する例です。

SQL> CREATE TABLE gen1 (id NUMBER GENERATED ALWAYS AS IDENTITY, val VARCHAR2(10)) ;
Table created.

SQL> INSERT INTO gen1(val) VALUES ('data1') ;
1 row created.

SQL> SELECT * FROM gen1 ;

        ID VAL
---------- ----------
         1 data1

SQL> UPDATE gen1 SET id = 2 WHERE id=1 ;
UPDATE gen1 SET id = 2 WHERE id=1
                *
ERROR at line 1:
ORA-32796: cannot update a generated always identity column

GENERATED BY DEFAULT AS IDENTITY

自動採番された値は更新できます。自動採番列に対するUPDATE文は成功します。

SQL> CREATE TABLE gen2 (id NUMBER GENERATED BY DEFAULT AS IDENTITY, val VARCHAR2(10)) ;
Table created.

SQL> INSERT INTO gen2(val) VALUES ('data1') ;
1 row created.

SQL> SELECT * FROM gen2 ;
        ID VAL
---------- ----------
         1 data1

SQL> UPDATE gen2 SET id = 2 WHERE id=1 ;
1 row updated.

内部的な動作

内部的にはSEQUENCEオブジェクトが作成され、列のデフォルト値として指定されています。

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

COLUMN_NAM IDE DATA_DEFAULT
---------- --- ----------------------------------------
ID         YES "SCOTT"."ISEQ$$_80368".nextval

SEQUENCEオブジェクトの作成のため、GENERATED AS IDENTITY列を作成する場合には、CREATE SEQUENCEシステム権限が必要になります。下記はシステム権限が無い場合のエラー・メッセージです。

SQL> CREATE TABLE gen3 (id NUMBER GENERATED BY DEFAULT AS IDENTITY, val VARCHAR2(10)) ;
CREATE TABLE gen3 (id NUMBER GENERATED BY DEFAULT AS IDENTITY, val VARCHAR2(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

自動作成されるSEQUENCEは直接値を更新することができます。このためシーケンス値には番号の抜けが発生する可能性があります。

SQL> SELECT * FROM gen1 ;

        ID VAL
---------- ----------
         1 data1

SQL> SELECT ISEQ$$_80368.NEXTVAL FROM DUAL ;

   NEXTVAL
----------
         2

SQL> INSERT INTO gen1(val) VALUES ('data2') ;

1 row created.

SQL> SELECT * FROM gen1 ;

        ID VAL
---------- ----------
         1 data1
         3 data2

トランザクションをロールバックしてもシーケンス値は更新されます。

SQL> INSERT INTO gen2(val) VALUES ('data3') ;

1 row created.

SQL> ROLLBACK ;

Rollback complete.

SQL> INSERT INTO gen2(val) VALUES ('data3') ;

1 row created.

SQL> COMMIT ;

Commit complete.

SQL> SELECT * FROM gen2 ;

        ID VAL
---------- ----------
         1 data1
         2 data2
         4 data3

自動生成されたSEQUENCEオブジェクトは、NEXTVALによる直接変更はできますが、ALTER SEQUENCE文で属性を変更することはできません。ALTER TABLE文を使用します。

SQL> ALTER SEQUENCE ISEQ$$_80368 START WITH 10 ;
ALTER SEQUENCE ISEQ$$_80368 START WITH 10 ;
                            *
ERROR at line 1:
ORA-32793: cannot alter a system-generated sequence

SQL> ALTER TABLE gen1 MODIFY (id GENERATED ALWAYS AS IDENTITY (START WITH 10)) ;
Table altered.