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.
```