PostgreSQLの少なくとも7.4以降では、CREATE TABLE実行時に "serial" を型に指定すると、対応するシーケンスを自動生成してnextval()してくれるようになります。
今回は、連番キー値が格納されるが主キー設定もされていないしシーケンスも使われていないカラムに対し"serial"型のような自動採番を設定する方法についてメモしておきます。
つまり、
CREATE TABLE foo (id integer, name text);
これを、
CREATE TABLE foo (id serial PRIMARY KEY, name text);
したことにします。
なお動作確認は PostgreSQL 8.1 です。
とりあえずテーブルを作ってみます。
CREATE TABLE foo (id integer, name text); INSERT INTO foo (id, name) VALUES (1, 'abc'); INSERT INTO foo (id, name) VALUES (3, 'def');
この"id"カラムに対して自動採番を設定していきます。
まず主キー制約を作成します。
ALTER TABLE ONLY foo ADD CONSTRAINT foo_pkey PRIMARY KEY (id);
続いてidの型をserialに・・・するのは間違いです。ALTER TABLE からでは "serial" を指定出来ません。
ALTER TABLE foo ALTER COLUMN id TYPE serial; → ERROR: type "serial" does not exist
正解は、1.sequenceを作成し、2.DEFAULT値にnextval()を設定します。
sequence名は「テーブル名 + "_" + カラム名 + "_seq"」にしておくと無難です。
CREATE SEQUENCE foo_id_seq; ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq');
最後に、現在のsequence値を設定します。
SELECT setval('foo_id_seq', 3); or SELECT setval('foo_id_seq', 4, false);
以上で自動採番が使えるようになります。
ただし、これは
id serial PRIMARY KEY
と完全に同義ではありません。
serialの場合はpg_dumpが出力するCREATE TABLEにおいても
id serial
となり、CREATE SEQUENCEはpg_dumpの出力には含まれません。
シーケンス値の設定も
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('テーブル名', 'カラム名'), xxxx, true);
として設定されます。
つまり"serial"にすると、シーケンスの生成や破棄がテーブルと連動するよう裏側で調整されます。
一方、上記の手順に従い手動で設定した場合は
id ... DEFAULT nextval(...) ...
となり、CREATE SEQUENCE文+setval() がpg_dumpの出力に含まれます。シーケンスの生成・破棄はテーブルと連動せず、独立して管理されます。
PostgreSQL 8.2 以降であれば、
ALTER SEQUENCE シーケンス名 OWNED BY テーブル名 + "." + カラム名
を使うことで、serialを指定した時と同様、テーブルと連動するよう後から変更出来ます。
ざっと調べてみたところ、8.1以前は ALTER SEQUENCE ... OWNED BY が使えないようです。
また、これに同等なSQLなども見当たらなかったため、8.1以前ではserialと同様にテーブルと連動するシーケンスを後付けで設定することは出来ないと思われます。
まとめると、
便利な "serial" ですが、適用できないケースもあります。
"serial"指定によりテーブルと連動するシーケンスは、1:1の関係で裏側で連動する為、他のテーブルから自由に使うのが難しくなります。
例えばテーブルとDROPすると、連動していたシーケンスも自動的にDROPされます。他のテーブルからそのシーケンスを使っていると困ったことになります。
よって、一つのシーケンスを複数のテーブルの自動採番に使いたい場合は、"serial"は使わずに、今回のメモのように手動でnextval()をカラムに設定すると良いでしょう。