Oracle カラム値の自動採番

内部キーを自動生成すべくOracleの自動採番機能を試します。11gまでのバージョンでは事前にシーケンスを作成しておいて、insertのタイミングでnextvalしていく方法がオーソドックスでしたが、先日あるトランザクションテーブルを新設する際に実装してみました。create tableで自動採番の定義をするわけですが内部的にはcreate tableのタイミングでシーケンスが作成されてこれがinsertの都度nextvalされていく仕組みです。基本的にはこれまで手動でやっていた方法と変わらないみたいですが、内部的に作成されたシーケンスに対してalter sequenceを発行する事はできないのでalter tableで変更する事となります。

またこの自動採番機能を適用したテーブルに対してinsert allは実質使えなくなる点も注意が必要ですね。こちらご参考にどうぞ。

create table で　GENERATED ALWAYS/BY DEFAULT を定義します

この表はOracleサイトから引用しています。
https://docs.oracle.com/cd/F37626_01/java-driver-table/creating-tables-identity-column.html

CREATE TABLE EX01
   (    
     SID      NUMBER(18,0)
              --GENERATED  ALWAYS AS IDENTITY
              GENERATED BY DEFAULT AS IDENTITY 
              MINVALUE 1 
              MAXVALUE 999999999999999999
              INCREMENT BY 1 
              START WITH 1 
              CACHE 100
              ORDER 
              NOCYCLE
              NOT NULL
    ,SNAME    VARCHAR2(32 CHAR)
    ,BIRTHDAY DATE
    ,NOTE     VARCHAR2(256 CHAR)
    ,PRIMARY KEY (SID)
   )
;

DBA_TAB_IDENTITY_COLSでカラム名と対応するシーケンスの関係を確認できます

IDENTITYで定義したカラムとシーケンスの関係はDBA_TAB_IDENTITY_COLSで確認できます。また、sequenceに関する情報を変更するにはalter sequenceではなく、alter tableでの変更になります。

select * from DBA_TAB_IDENTITY_COLS
;

SQL> select t.COLUMN_NAME,t.SEQUENCE_NAME from DBA_TAB_IDENTITY_COLS t
  2  where t.OWNER='SOONI' and t.TABLE_NAME='EX01'
  3  ;

   COLUMN_NAME    SEQUENCE_NAME
______________ ________________
SID            ISEQ$$_100050

SQL> select s.MIN_VALUE,s.MAX_VALUE,s.INCREMENT_BY,s.LAST_NUMBER,s.CACHE_SIZE,s.ORDER_FLAG,s.CYCLE_FLAG
  2   from dba_sequences s where s.SEQUENCE_NAME='ISEQ$$_100050'
  3  ;

   MIN_VALUE             MAX_VALUE    INCREMENT_BY    LAST_NUMBER    CACHE_SIZE    ORDER_FLAG    CYCLE_FLAG
____________ _____________________ _______________ ______________ _____________ _____________ _____________
           1    999999999999999999               1              1           100 Y             N

SQL>
SQL> alter SEQUENCE ISEQ$$_100050 cache  40
  2  ;

次のコマンドの開始中にエラーが発生しました : 行 1 -
alter SEQUENCE ISEQ$$_100050 cache  40
エラー・レポート -
ORA-32793: システム生成の順序は変更できません
32793.0000 -  "cannot alter a system-generated sequence"
*Cause:    An attempt was made to alter a system-generated sequence.
*Action:   A system-generated sequence, such as one created for an
           identity column, cannot be altered.
SQL> alter table ex01  MODIFY ( SID GENERATED BY DEFAULT AS IDENTITY CACHE 40 )
  2  ;

Table EX01が変更されました。

SQL> select s.MIN_VALUE,s.MAX_VALUE,s.INCREMENT_BY,s.LAST_NUMBER,s.CACHE_SIZE,s.ORDER_FLAG,s.CYCLE_FLAG
  2   from dba_sequences s where s.SEQUENCE_NAME='ISEQ$$_100050';

   MIN_VALUE             MAX_VALUE    INCREMENT_BY    LAST_NUMBER    CACHE_SIZE    ORDER_FLAG    CYCLE_FLAG
____________ _____________________ _______________ ______________ _____________ _____________ _____________
           1    999999999999999999               1              1            40 Y             N

SQL>

DBA_SEQUENCESと結合するとシーケンス情報を確認できます

select  t.OWNER,t.TABLE_NAME,t.SEQUENCE_NAME,s.CACHE_SIZE,s.LAST_NUMBER
from DBA_TAB_IDENTITY_COLS t inner join DBA_SEQUENCES s
on t.OWNER = s.SEQUENCE_OWNER and t.SEQUENCE_NAME= s.SEQUENCE_NAME
where t.TABLE_NAME ='EX01' and t.OWNER='SOONI'
;

GENERATED ALWAYS の場合、「カラム値」を指定したINSERTはできない。

ALTER TABLE SOONI.EX01 MODIFY (SID GENERATED ALWAYS AS IDENTITY (START WITH 1000000000)) 
;

GENERATED BY DEFAULT の場合、「カラム値」を指定したINSERTが可能

ALTER TABLE SOONI.EX01 MODIFY (SID GENERATED BY DEFAULT AS IDENTITY (START WITH 1000000000)) 
;

SQL> select t.OWNER,t.TABLE_NAME,t.GENERATION_TYPE
  2  from DBA_TAB_IDENTITY_COLS t
  3  where t.OWNER='SOONI' and t.TABLE_NAME='EX01'
  4  ;

   OWNER    TABLE_NAME    GENERATION_TYPE
________ _____________ __________________
SOONI    EX01          ALWAYS

SQL>
SQL> insert into EX01
  2  (sid,SNAME, BIRTHDAY, NOTE)
  3  values(10,'西川','2001/01/08','一番の長身')
  4  ;

次のコマンド行の開始中にエラーが発生しました : 1 -
insert into EX01
(sid,SNAME, BIRTHDAY, NOTE)
values(10,'西川','2001/01/08','一番の長身')
コマンド行 : 2 列 : 2 でのエラー
エラー・レポート -
SQLエラー: ORA-32795: GENERATED ALWAYSで作成されたアイデンティティ列には挿入できません
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.
SQL> alter table ex01 MODIFY (SID GENERATED BY DEFAULT AS IDENTITY)
  2  ;

Table EX01が変更されました。

SQL> insert into EX01
  2  (sid,SNAME, BIRTHDAY, NOTE)
  3  values(10,'西川','2001/01/08','一番の長身')
  4  ;

1行挿入しました。

SQL>

自動採番カラムの追加

以下DDLでカラムの追加と、既存レコードが存在する場合自動採番した値を入れてくれます。

ALTER TABLE SOONI.EX01 add (SID  NUMBER(18,0) GENERATED BY DEFAULT AS IDENTITY (START WITH 1000000000)) 
;

シーケンスキャッシュ値を変更する例

割り当たっているシーケンスのシーケンスキャッシュ値を変更する場合以下のようになります。

ALTER TABLE SOONI.EX01 modify (SID GENERATED BY DEFAULT AS IDENTITY (CACHE 300)) 
;