HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

identity column - テーブルの各行にシーケンス番号を割り当てる

ある理由から、シーケンス番号をテーブルの各行に割り当てる必要があり、シーケンスジェネレータを明示的に生成(CREATE SEQUENCE)して、テーブルへの行挿入時にトリガーを使ってシーケンス番号を割り当てるというSQLコードを書いた。
そのコードに対する意見を求めたところ、「identity columnというのがある」という意見をもらったので早速調べてみた。
identity columnの機能のみに着目するため、それ以外の事項は無視すると、identity columnを使用するには、テーブル定義を次のように行う。

CREATE TABLE hoge(
      seq_num INTEGER GENERATED ALWAYS AS IDENTITY
    , msg VARCHAR(32) NOT NULL
)

または、

CREATE TABLE foo(
      seq_num INTEGER GENERATED BY DEFAULT AS IDENTITY
    , msg VARCHAR(32) NOT NULL
)

前者と後者の違いは、"ALWAYS"となっているか"BY DEFAULT"となっているかという点である。
"ALWAYS"の場合は、identity columnへの値の挿入はDBMSが行うことになるため、行の挿入は次のように行う。

INSERT INTO hoge(msg)
    VALUES('01: first message')

一方、"BY DEFAULT"の場合は、identity columnに対する値が指定されない場合にシーケンス番号を割り振る。そのため次のような行の挿入も可能となる。

INSERT INTO foo(seq_num, msg)
    VALUES(10, '01: first message with seq# 10')

それぞれのテーブルにいくつかの行を追加したあとにテーブルの内容を表示してみると、次のようになる。(msg列の値の先頭にある2桁の数字は挿入した順序)

SELECT seq_num, msg from hoge order by msg

SEQ_NUM     MSG
----------- --------------------------------
          1 01: first message
          2 02: second message
          3 03: third message
          4 04: fourth message
          5 05: fifth message
          6 06: sixth message
          7 07: seventh message
          8 08: eighth message
SELECT seq_num, msg from foo order by msg

SEQ_NUM     MSG
----------- --------------------------------
         10 01: first message with seq# 10
         10 02: second message with seq# 10
          1 03: third message
          2 04: fourth message
          1 05: fifth message with seq# 1
          3 06: sixth message
          4 07: seventh message with seq# 4
          4 08: eighth message

見てわかるように、"ALWAYS"の場合、内部的なシーケンスジェネレータがidentity columnの値を生成する唯一のものなので、単調増加する整数が順にidentity columnに設定される(もちろん、明示的なシーケンスジェネレータと同様に、オプションによって初期値、増分などは変更できる)。
一方、"BY DEFAULT"の場合、内部的なシーケンスジェネレータが生成する値が使用されるのはidentity columnに明示的に値を指定しなかった場合なので、上記のような結果を生ずることもできる。
注意しなければいけないのは、identity columnとは言っても、その値が一意であることを保証するわけではないということ。値の一意性はユニーク制約や主キー制約などで別途保証することになる。


実際に実行したクエリを保存したファイルは次のとおり。


ちなみに、上記のSQLコードは標準SQLに基づいたものなのだが、各DBMSでの実行結果は次のようになった。

DBMS 結果
DB2 Express-C v9.7
Oracle 10g XE ×
PostgreSQL v8.4 ×
MySQL v5.1 ×
Firebird v2.1 ×
SQL Server 2008 Express ×

すべてテーブルすら生成できないらしい。
ここまでだめだと、各DBMSごとに同等の機能を持つ構文を調べないとだめらしい。