人工キー（主キー自動生成）について、おさらい

主キー(プライマリキー)を自動生成する方法について、おさらいしてみました。

「主キー」には「自然キー」と「人工キー」があり*1、それぞれ

自然キー

システムの外部で一意となる属性を利用したキー

人工キー

自然キーが持てない場合にシステム内部で生成するキー

のような意味です。（一部、Wikipediaを参考にしています。）
自然キーの例としては、社員番号やURIなどのIDがあります。

人工キーは、外部から意味のあるキーが渡されないようなケース、たとえばログのような情報は、キーにできそうな情報はタイムスタンプくらいしかありません。
ところが、主キーは一意＝ユニークであることが確実でなければならず、タイムスタンプだけでは一意性に難があります。

ユニークなキーを生成する一般的な方法として、

アトミックに連番を生成（シーケンス）
グローバルな一意IDの生成（UUID,GUID）
O/Rマッパーの機能を使う

などがあります。

これを標準SQLでどのRDBMSでも同じようにできたら理想的。
現実は、標準SQL規格のSQL:2003でやっとID型が導入されたものの、対応状況は芳しくなく、どの製品でも同じようにとはいかないようです。

アトミックに連番を生成（シーケンス）

アトミックに連番を生成する、安全かつ簡単な方法は、シーケンスを利用することです。
テーブル列と同期させるのも容易です。
ただし、RDBMS製品によって操作が異なるのが難点です。

ログを登録するテーブルを作ることにしましょう。このテーブルTABLE1の仕様は次のとおり。

第1キー: 現在のタイムスタンプを"YYYYMMDDHH24MISS"にフォーマットした文字列(14桁)。日付の範囲は、西暦0〜9999年。TIMESTAMPでも構わないが桁を揃えたいので文字列で。
第2キー: シーケンス。範囲は0〜9999、CYCLIC。

それでは、いくつかのRDBMSで*2試していきます。

PostgreSQLでは、連番型という自動連番サポートがあります。
これは、シーケンス＋デフォルト値＋インデックスをまとめて作成してくれる機能*3です。

参考：8.1.4. 連番型 (PostgreSQL 9.1.3文書)

-- PostgreSQL 8.3
CREATE TABLE TABLE1 (
  K1 CHAR(14) DEFAULT to_char(clock_timestamp(), 'YYYYMMDDHH24MISS'),
  K2 SERIAL,  -- integer (4 bytes)
  V1 VARCHAR(99),
  PRIMARY KEY (K1, K2)
);
ALTER SEQUENCE TABLE1_K2_SEQ
  MINVALUE 0 MAXVALUE 9999 CYCLE;

INSERT INTO TABLE1 (V1) VALUES ('MSG');

（2番目の"ALTER SEQUENCE"は仕様を揃えるためなので、無くてもかまいません。）
SERIAL型が連番型です。
SERIAL型を使わずに書くと、こうなります。

-- PostgreSQL 8.3
CREATE SEQUENCE TABLE1_K2_SEQ
  MINVALUE 0 MAXVALUE 9999 CYCLE;
CREATE TABLE TABLE1 (
  K1 CHAR(14) DEFAULT to_char(clock_timestamp(), 'YYYYMMDDHH24MISS'),
  K2 INTEGER NOT NULL DEFAULT NEXTVAL('TABLE1_K2_SEQ'),
  V1 VARCHAR(99),
  PRIMARY KEY (K1, K2)
);

JavaDB(Apache Derby)にも、自動連番型があります。

参考：発番列仕様 (Derby Reference Manual ja_JP)
- Autogenerated keys (Derby Reference Manual)

-- JavaDB (Apache Derby 10.8)
CREATE TABLE TABLE1 (
  K1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  K2 INT GENERATED ALWAYS AS IDENTITY,
  V1 VARCHAR(99),
  PRIMARY KEY (K1, K2)
);

INSERT INTO TABLE1 (V1) VALUES ('MSG');

（JavaDBは日付の文字列変換が面倒なのでTIMESTAMPにしています。）
前述のドキュメントによると、連番はシステム表のSYS.SYSCOLUMNSで管理されているようです。

Oracleでは、列のDEFAULTにシーケンスの払出し(NEXTVAL)を使用できない仕様(バージョン11.2時点)のため、INSERT文に書くしかない*4ようです。

-- Oracle Database 11.1
CREATE SEQUENCE TABLE1_K2_SEQ
  MINVALUE 0 MAXVALUE 9999 CYCLE;

CREATE TABLE TABLE1 (
  K1 CHAR(14) DEFAULT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),
  K2 NUMBER(4), -- 4 digits
  V1 VARCHAR2(99),
  PRIMARY KEY (K1, K2)
);

INSERT INTO TABLE1 (K1, K2, V1)
  VALUES (TABLE1_K2_SEQ.NEXTVAL, 'MSG');

他にも、例えばMS-Accessでは、「オートナンバー型」という自動連番型があります。

グローバルな一意IDの生成（UUID,GUID）

UUIDはUniversal Unique Identifier、日本語では汎用一意識別子と表記されます。

UUIDは、分散システム上でどこかが統制を取らずとも、一意に特定可能な識別子の作成を目的としており、UUIDは重複や偶然の一致が起こりえないと確信して用いることができる。
汎用一意識別子 - Wikipedia

DBに限らず、分散システムにおいて、ID管理のための集中管理の仕掛け（例えばシーケンス）が不要、というのが特徴です。

PostgreSQLでは、バージョン8.3からUUID型をサポートしています。が、生成関数については、コアモジュールには含まれていないようです。uuid-osspという追加モジュールを入れれば使えるようになるようです（未確認）。

参考：8.12. UUID型 (PostgreSQL 9.1.3文書)

JavaDB(Apache Derby)では、UUID1(time-based)を文字列で返すメソッドを作って、それをSQL関数に設定するのが良さそうです。

-- JavaDB (Apache Derby 10.8)
CREATE FUNCTION UUID1() RETURNS CHAR(36)
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'net.argius.UUIDGenerator.timeUUIDString';

SELECT UUID1() FROM SYSIBM.SYSDUMMY1;

Firebirdでは、2.1からGEN_UUIDがサポートされたようです。

参考：Does Firebird support UUIDs or GUIDs? (The Firebird FAQ)

2.5からは、UUID_TO_CHAR関数も使えるようになったようです。

参考：UUID_TO_CHAR() (Firebird 2.5 Language Ref. Update)

-- Firebird 2.1
SELECT GEN_UUID() FROM RDB$DATABASE;
-- Firebird 2.5
SELECT UUID_TO_CHAR(GEN_UUID()) FROM RDB$DATABASE;

Oracleでは、SYS_GUID関数を使います。結果はRAW(16)で返されます。

-- Oracle Database 11.1
SELECT SYS_GUID() FROM DUAL;
CREATE TABLE TABLE2 ADD (
  K1 RAW(16) DEFAULT SYS_GUID(),
  PRIMARY KEY (K1));

実装詳細は不明ですが、連続して実行すると6バイト目(UUIDで言うところのtime_midの下位バイト)がカウントアップされました。

SQLServerやAccessでは、データベースの分散などで使われるレプリケーションID型というものがあり、これにGUID(UUIDのMS実装)が用いられています。

UUID生成関数はさまざまな言語で提供されていますので、互換性のないSQLで操作するよりも、CHAR(32)ないしCHAR(36)の列に文字列としてアプリケーション側で追加するようにしたほうが楽かもしれません。
...とは言ったものの、実装の細かいところは挙動が全然違ったりするので、異なるUUID実装を使って同じマシン上でUUID1を取得するのは避けたほうが良さそうです。

上記のハイブリッド − O/Rマッパー

おそらくほとんどのO/Rマッパーで(EJBのEntityBeanでさえ)、ID生成をサポートしているようです。
ID生成属性を指定すると、自動でやってくれたり選択可能な生成方式の候補−例えば、シーケンス方式、UUID方式、等々−を出してくれたりします。
O/Rマッパーによっては、独自の生成方式（有名なところでは、Hibernateのhiloとか？）もあります。

まとめ

それぞれの長所と短所をざっと挙げてみます。

シーケンス
- 長所：一般的で手軽、登録順が確保できる
- 短所：SQL互換性がない、シーケンスに依存
UUID
- 長所：(発番システムの)一元管理が不要、SQL以外でも発番可能
- 短所：サイズが冗長(常に16バイト)、UUID関数の実装詳細が微妙
O/Rマッパーの機能
- 長所：選択肢が多い、O/Rマッパーの中で"ID発行"としてラッピングされている
- 短所：O/Rマッパーごとに異なる、O/Rマッパーの外で使えない

O/Rマッパーを使っているなら、O/Rマッパーの機能を使うのが良さそうです。
ただし、オンラインとバッチの両方で使われるケースや、異なるシステム間でテーブルを共有したりするケースなどの場合のように、O/Rマッパーを使えないケースもあるかと思います。

*1:「主キー」も「自然キー」も「人工キー」も、ふだん使わない言葉なので違和感があります。

*2:それにしても某社製品ばかりに。これにMy〜も入れたら大変なことに？

*3:バージョンによって詳細は異なります。

*4:トリガを使えば実現できますが、いずれにしても互換性がありません。

argius note

プログラミング関連

人工キー（主キー自動生成）について、おさらい

アトミックに連番を生成（シーケンス）

グローバルな一意IDの生成（UUID,GUID）

上記のハイブリッド − O/Rマッパー

まとめ