サロゲートキーはなぜ必要なのか — 変わるコードと、保つ参照を分ける

LWP | サロゲートキーはなぜ必要なのか―変わるコードと、保つ参照を分ける

サロゲートキーはなぜ必要なのか―変わるコードと、保つ参照を分ける

顧客の同一性、元システムのコード、履歴の版を、同じ一つのキーに押し込めない。

ストーリー

シャケモンは、顧客コードを変えたいという要望を受け、注文との参照まで変える必要があるかを考えます。アユさんと、業務で使うコードと構造上のIDを分け、さらに複数システムの統合、所在地の履歴、メモリ上のオブジェクトへ問いを広げます。何を「同じ」と保つのかが、キーを選ぶ出発点になります。

会社名、コード、ID、日付、金額は架空の説明用データです。顧客82517の注文9001・9002の金額100・200は単位を定めない説明値です。履歴例では顧客50001の有効期間を重複させず、開始日を含み終了日を含まないものとします。

1 顧客コードが変わると、注文は？

図1｜顧客コードC001をTK-C001へ変更しても、注文が参照するCustomerID 82517は同じ値を保つ。

2 PK・FKと、キーの由来は別

図2｜PK・FKは役割、自然キー・サロゲートキーは値の由来であり、同じ軸の選択肢ではない。

3 コードを変えても、参照は同じ

図3｜CustomerID 82517を保ったままコードを変更すると、注文2件・合計300の参照は保たれる。

4 別IDなら、業務の重複は防げる？

図4｜101と102という異なるIDで同じコードC001を登録できてしまう。業務上の一意性は別の制約で守る。

5 コードの意味には、出所もある

図5｜システムAのC001とシステムBのC001は、コード文字列が同じでも同じ顧客とは限らない。

6 同じ顧客だと、どう判断する？

図6｜AのC001とBのX900をABC商事として50001へ対応させ、BのC001のDEF商事は50002へ分ける。

7 顧客の同一性と、履歴1行

図7｜SCD Type 2では顧客の持続ID50001を保ち、東京・大阪・京都の各版へ異なる行キーを割り当てる。

8 取引日で、対応する版を選ぶ

図8｜取引を持続IDだけで履歴全行へ結合すると6行・合計900となる。期間で対応版を選ぶと2行・合計300となる。

9 3つのキーが答える問い

図9｜元システムのコード、顧客を保つ持続ID、履歴の版を識別する行キーは、それぞれ別の問いに答える。

10 DBのIDと、オブジェクト参照

図10｜同じDB行82517を表す2つのオブジェクトでも、メモリ上の参照が同じとは限らない。

11 必要な場面で、分離する

図11｜コード変更、複数システム統合、履歴管理の要件と、追加ID・対応表・制約の管理コストを比べて採用する。

まとめ

PK・FKはキーの役割、自然キー・サロゲートキーは値の由来です。サロゲートキーを使っても、業務キーの一意性、名寄せの判断、履歴期間の整合性は別に守る必要があります。顧客を保つ持続IDと履歴1行のキーを分け、取引時点の版を選ぶと、誤った多重結合を防げます。全ての表へ追加IDを導入せず、一行の意味、変更、統合、履歴の要件と管理コストから判断します。

今回の仕掛け

82517はコードを変えても保持する参照IDとして扱い、注文2件・合計300を保ちました。サロゲートキーで構造上の参照と業務上の制約が両方解決する、という説明にはしていません。

複数システムのコードを統合する段では、同一顧客の照合と採番を分けました。持続IDの考え方はKimballのDurable Keysの説明へ照合しています。

履歴版の行キーと有効期間の説明は、Microsoft公式のSCD Type 2の説明も参照しています。取引時点の所在地と現在の所在地では、集計が答える問いが違います。

DB行のIDとメモリ上の参照の区別は、Martin FowlerのIdentity Fieldを参照しました。ORMが同一インスタンスを返す範囲は実装や管理範囲に依存します。

今回のソースコードのサンプル

サンプルコード業務コードを変更し、IDによる注文参照を保つ（SQLite）


CREATE TABLE customer (
  customer_id INTEGER PRIMARY KEY,
  customer_code TEXT NOT NULL UNIQUE,
  customer_name TEXT NOT NULL
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customer(customer_id),
  amount INTEGER NOT NULL
);
INSERT INTO customer VALUES (82517, 'C001', 'ABC商事');
INSERT INTO orders VALUES (9001, 82517, 100), (9002, 82517, 200);
UPDATE customer SET customer_code = 'TK-C001'
WHERE customer_id = 82517;
SELECT c.customer_id, c.customer_code,
       COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount
FROM customer c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_code;

01	`PRAGMA foreign_keys = ON;`
02	`CREATE TABLE customer (`
03	`customer_id INTEGER PRIMARY KEY,`
04	`customer_code TEXT NOT NULL UNIQUE,`
05	`customer_name TEXT NOT NULL`
06	`);`
07	`CREATE TABLE orders (`
08	`order_id INTEGER PRIMARY KEY,`
09	`customer_id INTEGER NOT NULL REFERENCES customer(customer_id),`
10	`amount INTEGER NOT NULL`
11	`);`
12	`INSERT INTO customer VALUES (82517, 'C001', 'ABC商事');`
13	`INSERT INTO orders VALUES (9001, 82517, 100), (9002, 82517, 200);`
14	`UPDATE customer SET customer_code = 'TK-C001'`
15	`WHERE customer_id = 82517;`
16	`SELECT c.customer_id, c.customer_code,`
17	`COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount`
18	`FROM customer c JOIN orders o ON o.customer_id = c.customer_id`
19	`GROUP BY c.customer_id, c.customer_code;`

関数の説明

関数を定義する例ではなく、顧客表と注文表の一意性・参照を宣言し、コード変更後に注文を集計するSQLです。

コードの説明

customer_idは顧客表で主キー、注文表で外部キーです。customer_codeはこの一顧客一行の表では必須かつ一意とし、別のIDで同じ業務コードを登録することを防ぎます。PRAGMAはSQLiteで外部キー検査を有効にする指定です。

入力・概要

空のSQLiteデータベースで一度実行する例です。既存表へそのまま追加実行しません。顧客82517へ、金額100・200の注文2件を対応させています。SCD Type 2の履歴表とは異なる、現在の顧客を一行で持つ表です。

結果

customer_id=82517、customer_code=TK-C001、order_count=2、total_amount=300という1行になります。コード変更後も注文の外部キーは82517のままです。

読むポイント

値の由来とPK・FKの役割を別々に読みます。コードを主キーにする設計も可能で、変更時の方針が必要です。本例の実行と、一意性・参照整合性の拒否、履歴結合の300対900の差は、隔離したSQLiteで確認しています。

出典メモ

元ネタ：[採用原稿 260910_サロゲートキーはなぜ必要なのか ― 構造上のIDと業務上のキーを分離する.md](<C:/LWP/記事制作/01_記事ネタ/archive/260910_サロゲートキーはなぜ必要なのか ― 構造上のIDと業務上のキーを分離する.md>)。2026年10月4日に原稿全文を確認し、説明単位に沿って再構成しました。元ネタの原文は保全しています。人物・業務データは創作・説明用です。

図の文字・番号・参照・金額を目視確認し、SQLの例は隔離したSQLiteで実行しました。簿記例はこのモデルの計算と借貸一致を確認しています。実業務の帳簿や業務DBを検証した記事ではありません。

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。