自然キーとサロゲートキー
主キーには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制約で守る。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE books (
id integer PRIMARY KEY,
code text NOT NULL UNIQUE,
name text NOT NULL
);こうすれば、コードの重複は UNIQUE が防いでくれますし、コードが変わっても書き換えは1行です。両方の良いところを取れます。
例外は、prefectures のように値が変わらないことが制度で保証されていて、件数も増えないマスタです。こういうものは自然キーのままで構いません。
手を動かす
自然キー案でコード変更が起きたら何行を書き換えることになるかを数え、サロゲート案なら1行で済むことを確かめます。
商品コードが 'PC-001' から 'PC-001-V2' に変わりました。
- まず自然キー案の被害を数えます。
order_items_naturalのうちproduct_codeが'PC-001'の行が何行あるかを数えてください。この行数だけ書き換えが必要になります - 次にサロゲート案で対応します。
products_surrogateのid = 1の行のcodeを'PC-001-V2'に UPDATE してください。明細は触りません - 最後に
order_items_surrogateとproducts_surrogateを結合し、order_id、code、nameの3列をorder_id、同じ注文ではcodeの順に取り出してください
最後の結合は用意済みです。自然キー側の変更対象を数え、サロゲートキー側は商品だけを更新します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
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 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い