データベーススペシャリスト 2010年 午前2 問04

サロゲートキー導入【午前2解説】

正解の理由

解法ステップ

1. 「単一属性を主キーにする（ア・イ）」が成立するか検討：その属性だけで一意性が保証されるかを確認する。多くの場合は保証されないため不適。
2. 「サロゲートキーに置き換える（ウ・エ）」のうち、元の複合属性の扱いを確認：一意性を残すべきかどうか。単に外部キーにする（ウ）と一意性が失われるため不適。
3. 一意性を保持しつつ主キーを簡潔にする手法は、サロゲートキーをPKにして元の複合属性にユニーク制約を付ける（エ）。よってこれを選ぶ。

選択肢別の誤答解説

• ア: 「残りを外部キーにする」とあるが、外部キー（foreign key）は別テーブルの主キー参照に使うもので、自テーブル内の属性群を外部キーにするのは意味が合いません。さらに一意性が保証されず誤り。
• イ: 「残りをオルタネートキーにする」と読めるが、この選択肢は「複合キーのうち１つの属性を主キーにする」ことを前提としている。単一属性がそもそも一意を保てるなら可能だが、問題の前提は「属性数が多すぎる」＝単一属性で十分に識別できないケースが多い。重要な点として、オルタネートキー（alternate key）は「候補キーのうち選ばれなかった一意キー」を指し、候補キーであれば主キーに選ぶことも可能である。したがって「オルタネートキーは主キーの代替にはならない」と単純に否定するのは誤りで、用語を正確に区別して判断する必要があります。
• ウ: 「複合キーをサロゲートキーに置き換え、複合属性を外部キーにする」は不適切。元の複合属性は通常同じレコード内の属性であり、それらを外部キー（他テーブル参照）に変える意味がない。一意性も喪失する可能性が高い。
• エ: 「複合キーをサロゲートキーに置き換え、複合属性をオルタネートキーにする」は、主キーを簡潔にしても業務上必要な一意性を維持するための正しい設計であり、運用・パフォーマンス・整合性の観点で最も適切です。

よくある誤解

• 「オルタネートキーは主キーと別物で主キーになれない」：誤解です。オルタネートキーとは候補キー（candidate key）で、主キーに選ばれなかったものを指します。候補キーなら主キーに選べますし、逆に主キーにしない場合は一意制約（ユニーク）として残すことが一般的です。
• 「サロゲートキーを導入すると業務的意味が消えて全てダメになる」：サロゲートキーは識別を簡潔にするための手段です。業務的意味を持つ属性はオルタネートキーや追加カラムで保持し、必要なら参照用にインデックスやユニーク制約を付けて管理します。
• 「複合キーを外部キーにすれば参照は保たれる」：外部キーは参照関係を表すためのものであり、単に複合属性を外部キーに設定するのは設計意図を誤る可能性があります。参照元・参照先の関係を正しく設計することが重要です。

補足コラム

1. テーブルにサロゲートキー列を追加（SERIAL/IDENTITYやUUID）。
2. サロゲートキーを主キーに設定。
3. 元の複合属性に対してユニーク制約を追加（オルタネートキーとして保持）。
4. 子テーブルが元の複合キーで参照している場合は、子テーブルにサロゲートキー参照列を追加してデータ移行し、外部キー定義を更新する。

-- 1. サロゲートキー追加
ALTER TABLE orders ADD COLUMN orders_id BIGSERIAL;

-- 2. 主キー変更（既存のPKを削除した上で）
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ADD PRIMARY KEY (orders_id);

-- 3. 元の複合属性にユニーク制約を追加
ALTER TABLE orders ADD CONSTRAINT orders_unique_business UNIQUE (cust_id, order_date, item_code);

-- 4. 子テーブル移行の一例（簡略）
ALTER TABLE order_lines ADD COLUMN orders_id BIGINT;
UPDATE order_lines ol
SET orders_id = o.orders_id
FROM orders o
WHERE ol.cust_id = o.cust_id AND ol.order_date = o.order_date AND ol.item_code = o.item_code;
ALTER TABLE order_lines ADD CONSTRAINT fk_orderlines_orders FOREIGN KEY (orders_id) REFERENCES orders(orders_id);

FAQ