自然キーとサロゲートキー
主キーには2つの流派がある
主キーの選び方には、大きく2つのやり方があります。
自然キーは、そのデータがもともと持っている値をそのまま主キーにするやり方です。商品コード PC-001、都道府県名 東京都、メールアドレスなどが該当します。
サロゲートキーは、意味を持たない番号を新しく振るやり方です。id という列を作って 1、2、3 と振っていくものがこれです。代理キーとも呼びます。
自然キーの強み
自然キーには読みやすさがあります。order_items に product_code = 'PC-001' と書いてあれば、それだけで何の商品か想像がつきます。product_id = 7 では何も分かりません。
JOIN も減ります。第3章で prefectures の主キーを pref という text にしたのは、この理由でした。stores.pref を見るだけで都道府県名が分かるので、名前を知るための JOIN が要りません。
自然キーの弱み
弱みは1つだけですが、致命的です。その値が変わったときです。
商品コードの体系を変えることになり、PC-001 を PC-001-V2 に変えるとします。自然キー案では、その商品コードは注文明細にも在庫にも書かれています。売れた回数だけ、在庫を持つ店舗の数だけ、同じ文字列が散らばっています。
そのすべてを書き換えなければなりません。1か所でも漏れれば、そこは存在しない商品を指す行になります。
サロゲートキー案なら、products.code を1行書き換えるだけです。明細は product_id を持っていて、id は変わらないからです。明細は1行も触りません。
「絶対に変わらない」は信用しない
「商品コードは変わりません」と言われることがあります。設計の場面では、この言葉を信用しないほうが安全です。
会社が合併する。取引先の体系に合わせる。桁が足りなくなる。10年運用すれば、どれかは起きます。変わらないと言われたものが変わるのが業務システムです。
実務の落としどころ
多くの現場では、次のように使い分けています。
主キーはサロゲートキーにする。自然キーは
UNIQUE制約で守る。
CREATE TABLE products (
id integer PRIMARY KEY,
code text NOT NULL UNIQUE,
name text NOT NULL
);こうすれば、コードの重複は UNIQUE が防いでくれますし、コードが変わっても書き換えは1行です。両方の良いところを取れます。
例外は、prefectures のように値が変わらないことが制度で保証されていて、件数も増えないマスタです。こういうものは自然キーのままで構いません。
手を動かす
自然キー案でコード変更が起きたら何行を書き換えることになるかを数え、サロゲート案なら1行で済むことを確かめます。
テーブル構造
CREATE TABLE products_natural (
code text PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE order_items_natural (
order_id integer NOT NULL,
product_code text NOT NULL REFERENCES products_natural (code),
quantity integer NOT NULL,
PRIMARY KEY (order_id, product_code)
);
CREATE TABLE products_surrogate (
id integer PRIMARY KEY,
code text NOT NULL UNIQUE,
name text NOT NULL
);
CREATE TABLE order_items_surrogate (
order_id integer NOT NULL,
product_id integer NOT NULL REFERENCES products_surrogate (id),
quantity integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO products_natural VALUES
('PC-001', 'ノートPC'),
('AC-002', 'マウス');
INSERT INTO order_items_natural VALUES
(1, 'PC-001', 1),
(2, 'PC-001', 2),
(2, 'AC-002', 1),
(3, 'PC-001', 1);
INSERT INTO products_surrogate VALUES
(1, 'PC-001', 'ノートPC'),
(2, 'AC-002', 'マウス');
INSERT INTO order_items_surrogate VALUES
(1, 1, 1),
(2, 1, 2),
(2, 2, 1),
(3, 1, 1);
期待される出力
| order_id | code | name |
|---|---|---|
| 1 | PC-001-V2 | ノートPC |
| 2 | AC-002 | マウス |
| 2 | PC-001-V2 | ノートPC |
| 3 | PC-001-V2 | ノートPC |