X(Twitter) Zenn GitHub RSS 共有

サロゲートキーとナチュラルキーの選択

作成日時：2025-12-03
更新日時：2025-12-03

個人的なメモ。
テーブルの主キーはサロゲートキーにすべきか、ナチュラルキーにするかで考えたこと。

1.個人的結論

どちらを使うべきかは時と場合による。

個人的には基本ナチュラルキー。それで事足りるから。

サロゲートキーにすべき場合はサロゲートキーを使う。
その場合は、おおよそハイブリッドアプローチ(サロゲートキー+ナチュラルキーのユニーク制約)になる。

2.サロゲートキーにすべき場合

2.1.ナチュラルキーでエンティティを一意に識別できない

ナチュラルキーでエンティティを一意に識別できないなら、サロゲートキーを付けざるを得ない。
一意に識別できないならPKではないから。

2.2.ナチュラルキーが変更する可能性がある

数値型の店舗コードなのに、顧客都合で記号が入ることになったら？
その主キーを参照する外部キーを全て回収しなければならない。
基本的にテーブルの変更は、アプリケーションの変更に比べて面倒。

2.3.ナチュラルキーが長すぎる

複合主キーを構成するカラムが多い場合など。
子テーブルを持つ場合など、親テーブルの複合主キーを含めなければならなくなる。

下記の例だと、親子を関連付けるために、親のPK-c1~c5を子にも持たせるが冗長である。

Rp(c1, c2, c3, c4, c5, c6, c7)
Rc(c1, c2, c3, c4, c5, c8, c9, c10)

サロゲートキーならば持たせる必要はない。

Rp(S, c1, c2, c3, c4, c5, c6, c7)
Rc(S, c8, c9, c10)

また、ナチュラルキーの場合において、更新/削除時の条件設定漏れが発生する可能性がある。
上記の例で言えば、c1~c4までを指定してc5を忘れた場合、想定していないレコードが更新/削除される。

2.4.パフォーマンスを求める

主キーは複合キーや文字列よりは、単純な数値型のほうがいい。
比較も早いだろうし、検索やJOINのコストも下がる。
インデックスのサイズも小さくなるので、1ページに大量のデータが載る→キャッシュヒット率が向上する。

2.5.レコード数が極端に多い

理由は2.4と同様。
インデックスの効率が上がる。

3.サロゲートキーのメリット

4.サロゲートキーのデメリット

4.1.ストレージの消費

カラムが増えるのだから、ストレージは消費する。

4.2.インデックスが増える

もともとナチュラルキーが主キーならば、それはテーブルにおいて一意であるという制約がある。
サロゲートキーをただ付与すると、「ナチュラルキーがテーブルにおいて一意」という制約が消える。
それを回避するには、元のナチュラルキーにユニーク制約を付与しなければならない。

インデックスが増えることになるので、レコードの登録/更新/削除時にインデックスの更新コストが掛かる。

4.3.連番だとデータの存在を推測されやすくなる

DB設計ではなくアプリ側。
ユーザーを取得するREST APIのパスがGET /users/12なら、1-11の存在が推測できる。
この程度ならいいが、モノによってはなにかしらの攻撃のきっかけを与えかねない。

UUID(特にv7)をサロゲートキーにするのもいいかもしれないが、インデックスサイズが増大する。
ストレージの圧迫とキャッシュヒット率の低下につながる。
UUID v4だと時系列を持たないから範囲検索できないし、連続しているデータが複数ページに散らばりそう。

4.4.ログから追跡しづらくなる

これもアプリ側で個人的に思っていること。

例えば、リクエストログにGET /shops/1だったり、SQLログにSELECT * FROM shops WHERE id = 1と出力されている場合。
「1ってどこの店舗？」となる。
ナチュラルキーならばGET /shops/uenoやSELECT * FROM shops WHERE id = 'ueno'と出力されて、
「ああ、上野支店を参照しているのね」となる。

shopsテーブルを見ればいいだけの話だけれども。
ログとかの追跡性は後者のほうがわかりやすい。

5.雑記

どっちを選ぶかはエンティティの種類にもよるかな？
ヒト・モノはナチュラルキー。
コト(イベント)はサロゲートキー。
前者はナチュラルキーが存在しうるし、データ数も少なそう。
APIを叩くときのIDが直感的でわかりやすいし。
後者はそもそもナチュラルキーが存在しない場合もあるし、データも多い。

6.まとめ

システム開発に正解などない。
その時点における最善を追求すべき。