ID 列

ID 列には、表の行ごとに固有の数値が格納されます。 Db2 は、行が表に挿入されるときに、この列の順次数値を自動的に生成できます。 このため ID 列は、従業員番号やプロダクト番号など、主キー値に最適です。

キーとしての ID 列の使用

AS IDENTITY属性を使用し、GENERATEDALWAYS属性とNOCYCLE属性を使用して列を定義すると、Db2新しい行がテーブルに挿入されたときに、その列の値に対して単調に増加または減少する連続番号が自動的に生成されます。 ただし、Db2 がID 列の値がユニークであることを保証するには、その列にユニーク索引を定義しなければなりません。

ID 列は、一般には固有の連番である (例えば、 オーダー番号あるいは従業員番号) 主キーに使用できます。 そうすることによって、アプリケーションがデータベース外部にアプリケーション自身で固有のカウンターを 生成したときに生じることがある並行性の問題を避けることができます。

推奨 ：親テーブルを読み込んだ後に従属テーブルの外部キーの値を設定します。 参照保全構造体で ID 列を親キーとして使用する場合は、 その構造体へのデータのロードが非常に複雑になる可能性があります。 列が GENERATED ALWAYS として定義されているため、 この ID 列の値は、表がロードされるまで認識されません。

以下の理由から、ID 列の値にギャップが生じる場合があります。

  • ほかのアプリケーションが同じ ID 列に値を書き込む場合
  • Db2 がすべてのキャッシュされた値を割り当てる前に異常終了した場合
  • アプリケーションが、IDENTITY 値を挿入するトランザクションをロールバックする場合

ID 列の定義

ID 列は、GENERATED BY DEFAULT か GENERATED ALWAYS のいずれかとして 定義できます。

  • 列をGENERATEDBY DEFAULTとして定義すると、Db2値を挿入でき、指定しない場合はデフォルト値を提供します。
  • 列をGENERATEDALWAYSとして定義すると、Db2常に列の値が生成され、その列にデータを挿入することはできません。 その値を固有にする必要がある場合は、その ID 列を GENERATED ALWAYS および NO CYCLE で定義し、その列にユニーク索引を定義する必要があります。

ID列に対して生成される値Db2は、列がどのように定義されているかによって異なります。 START WITH オプションは、Db2 が生成する最初の値を決定します。 値は、INCREMENT BY 値によって、昇順または降順に進められます。

MINVALUE とMAXVALUE オプションは、 Db2 が生成する最小値と最大値を決定します。 ただし、CYCLEまたはNO CYCLEオプションは、値が昇順の場合はSTART WITH値とMAXVALUEの間、値が降順の場合はSTART WITH値とMINVALUEの間ですべての値を生成したときに、Db2値をラップするかどうかを決定します。 MINVALUE および MAXVALUE は、START WITH 値または RESTART WITH 値を制限しません。

例: GENERATED ALWAYS および CYCLE の使用

表 T1 は、GENERATED ALWAYS および CYCLE によって 定義されるものとします。

CREATE TABLE T1
  (CHARCOL1 CHAR(1),
   IDENTCOL1 SMALLINT GENERATED ALWAYS AS IDENTITY
     (START WITH -1,
      INCREMENT BY 1,
      CYCLE,
      MINVALUE -3,
      MAXVALUE 3));

ここで、以下の INSERT ステートメントを 8 回実行するとします。

INSERT INTO T1 (CHARCOL1) VALUES ('A');

Db2 IDENTCOL1の値を生成するときは、-1から始まり、5番目のINSERTでMAXVALUEの3に達するまで1ずつ増加します。 6番目のINSERTの値を生成するには、 Db2 MINVALUE（-3）に戻ります。 T1 は、8 回の INSERT ステートメントが終わると、 次のようになっています。

CHARCOL1  IDENTCOL1
========  =========
A                -1
A                 0
A                 1
A                 2
A                 3
A                -3
A                -2
A                -1

8 番目の INSERT の IDENTCOL1 の値は、 最初の INSERT の IDENTCOL1 の値を繰り返します。

変更の開始

例: 循環の範囲外の START WITH 値または RESTART WITH 値

MINVALUE オプションと MAXVALUE オプションは、START WITH 値を制限しません。 すなわち、START WITH 文節を使用して、循環に使用される範囲外の値の生成を開始できます。 ただし、指定された START WITH 値の後で次に生成される値は、昇順の ID 列の場合は MNVALUE、降順の ID 列の場合は MAXVALUE です。 ID 列を変更し、RESTART WITH 値を指定する場合も、これと同じことが当てはまります。

上記の例からの T1 を検討し、以下のキーワードを指定するステートメントで表を変更するものとします。

ALTER TABLE T1 
  ALTER COLUMN IDENTCOL1 SET GENERATED ALWAYS RESTART WITH 99;

ここで、以下の INSERT ステートメントを 3 回実行するとします。

INSERT INTO T1 (CHARCOL1) VALUES ('B');

Db2 IDENTCOL1値を生成するときは、99から始まります。 ただし、次に生成される値については、Db2再びMINVALUE（-3）に戻ります。 T1 は、3 回の INSERT ステートメントが終わると、 次のようになっています。

CHARCOL1  IDENTCOL1
========  =========
A                -1
A                 0
A                 1
A                 2
A                 3
A                -3
A                -2
A                -1
B                99
B                -3
B                -2
変更の終わり

主キーとしての ID 列

SELECT from INSERTステートメントを使用すると、Db2生成されたID列として定義されたプライマリーキーを使用して親表に行を挿入し、プライマリーキーまたは親キーの値を取得できます。 さらに、この生成された値を、従属表で外部キーとして使用できます。

その他に、IDENTITY_VAL_LOCAL 関数を使用すると、ある ID 列に対して ごく最近割り当てられた値を戻すことができます。

例: INSERT からの SELECT の使用

EMPLOYEE 表 および DEPARTMENT 表は、以下のように定義されるものとします。

CREATE TABLE EMPLOYEE
  (EMPNO      INTEGER GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY NOT NULL,
   NAME       CHAR(30) NOT NULL,
   SALARY     DECIMAL(7,2) NOT NULL,
   WORKDEPT   SMALLINT);

CREATE TABLE DEPARTMENT
  (DEPTNO     SMALLINT NOT NULL PRIMARY KEY,
   DEPTNAME   VARCHAR(30),
   MGRNO      INTEGER NOT NULL,
   CONSTRAINT REF_EMPNO FOREIGN KEY (MGRNO)
     REFERENCES EMPLOYEE (EMPNO) ON DELETE RESTRICT);

ALTER TABLE EMPLOYEE ADD
  CONSTRAINT REF_DEPTNO FOREIGN KEY (WORKDEPT)
     REFERENCES DEPARTMENT (DEPTNO) ON DELETE SET NULL;

EMPLOYEE 表に新しい従業員を挿入して、挿入されたその EMPNO 列の値を 取り出すには、以下の例に示す INSERT ステートメントから以下の SELECT ステートメントを使用できます。

EXEC SQL 
  SELECT EMPNO INTO :hv_empno
    FROM FINAL TABLE (INSERT INTO EMPLOYEE (NAME, SALARY, WORKDEPT)
                      VALUES ('New Employee', 75000.00, 11));

SELECT ステートメントは、ホスト変数 :hv_empno内の EMPNO 列に Db2が生成した ID 値を戻します。

さらに、:hv_empno の値を使用して、部門長として新しい従業員を指定して DEPARTMENT 表の MGRNO 列を更新できます。

EXEC SQL 
  UPDATE DEPARTMENT 
    SET MGRNO = :hv_empno
    WHERE DEPTNO = 11;