自動採番テーブルの補足

自動採番テーブルの補足
================================================================
■自動採番テーブル

□ 主キー項目などで、自動的に採番したい項目などは、「SERIAL」型
を指定します。なお、SERIAL型は、INTEGER型の数値として扱われます。
INSERTする度に、1ずつ加えられますが、連番を保障するものではあり
ません。

例：

CREATE TABLE sample (
    id SERIAL PRIMARY KEY, 
    data text
);

INSERT INTO sample VALUES(DEFAULT, 'test data1');

INSERT INTO sample(data) VALUES('test data2');

SELECT * FROM sample;
 id |    data
----+------------
  1 | test data1
  2 | test data2
(2 rows)


□ 現在のid列の値を取得する

SERIAL型の実体は、シーケンス・オブジェクトです。
このオブジェクト名は、「\d テーブル名」コマンドで取得できます。

\d sample
                         Table "public.sample"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id     | integer | not null default nextval('sample_id_seq'::regclass)
 data   | text    |
Indexes:
    "sample_pkey" PRIMARY KEY, btree (id)

または、「\ds」メタコマンドで、シーケンスの一覧が表示できます。

\ds
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | sample_id_seq | sequence | postgres
(1 row)

SERIAL型の実体のシーケンス名は、
「テーブル名_列名_seq」
になっています。

現在の値を取得するのは、CURRVAL関数を使います。

SELECT CURRVAL('sample_id_seq');
 currval
---------
       2
(1 row)

また、つぎにどの値が取得されるかは、NEXTVAL関数を使います。

select NEXTVAL('sample_id_seq');
 nextval
---------
       3
(1 row)

ただ、このとき、注意しないといけないのは、シーケンスにNEXTVAL関
数でアクセスすることで、値が更新される、ということです。単に、次
の値を予想したい場合は、CURRVAL関数の結果に1を加えたもので使うと
よいです。ただし、シーケンスは連番を保障していないので、あくまで
も予想です。実際に、NEXTVALを使ってデータを挿入してから、その値
を取得するとよいでしょう。


■外部キー制約があるテーブルへの行の挿入

つぎのような関係があるテーブルを想定します。

    ORDER → DETAIL

なお、矢印は、1対多の関係を表し、矢が「多」側 (外部キー制約を設
定しているテーブル)、矢じりが「1」側 (外部キー制約によって参照さ
れているテーブル)を表します。

基本的な形として、つぎのように手続きを書いていきます。
なお、これはJDBCを使った擬似コードのイメージなので、実際のプログ
ラミングでは、JavaとJDBCにしたがって書く必要があります。

---
START TRANSACTION;

INSERT INTO ORDER VALUES(DEFAULT, ...);

SELECT CURRVAL('order_id_seq');
int id = res.getInt();

while (...) {    // DETAILの行数分だけ
    INSERT INTO detail VALUES(DEFAULT, ?, ...);
    stmt.setInt(1, id);
}

COMMIT;
---

ここで、テーブルdetailの1列目はSERIAL型を使ったdetailテーブルの
主キー、2列目が、orderテーブルの主キーに関係した外部キーです。


                                                            以上