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ごとに同等の機能を持つ構文を調べないとだめらしい。