DB プライマリーキー設計で、ナチュラルキーとサロゲートキーはどっちが良いの？

はじめに

先日、データベースプライマリーキーの「ナチュラルキーとサロゲートキー」設計について、お話をしたので今後のためにまとめたいと思います。

結論

どちらが優れていると言う訳ではないので、「仕様」「状態」によって判断していきましょう！

※前のプロジェクトでは、「サロゲートキー」だったは判断理由として乏しいです。。。

ざっくり仕様的な判断材料

目的

• ナチュラルキー:
  • 現実世界で 一意に識別できる値 をそのまま主キーにする
    • 例：社員番号、メールアドレス、マイナンバーなど
• サロゲートキー:
  • システムが自動採番する一意のID をプライマリーキーにする
    • 例：数値型ID（AUTO_INCREMENT, SEQUENCE）、UUIDなど

ユースケース

• ナチュラルキー:
  • 既存業務で「一意の識別子」が明確に存在している場合
  • データを他システムと直接やり取りする場合（共通キーが存在する）
• サロゲートキー:
  • 現実の業務上のキーが 将来変わる可能性がある もの
  • 複数の候補キーが存在して選べない場合（業務上のキーが一意にできない）
  • 高速なJOINやインデックス最適化が求められる場合

メリット／デメリット

• ナチュラルキー:
  • メリット
    • データそのものが意味を持ち、直感的に理解できる
    • 重複や冗長なキーを避けられる（余計なIDを追加しない）
  • デメリット
    • キーの値が 将来的に変更される可能性 がある
    • 複数列で構成される複合キーになるケースが多く、JOINやインデックス効率が悪くなる
    • キーが長い（文字列や複合キー）場合、パフォーマンスやストレージに影響
• サロゲートキー:
  • メリット
    • 単純な整数キーなら 性能が安定 しやすい（JOINが速い、インデックス効率が良い）
    • 将来の業務変更やキー体系変更に強い（ビジネスキーが変わっても影響が少ない）
    • 外部キーでの参照がシンプル
  • デメリット
    • データそのものに意味がないため、キーだけでは何を表しているかわからない
    • ビジネス上の一意性は別に UNIQUE制約 で担保する必要がある
    • 余分なカラムが増える

補足

上記の「ざっくり仕様的な判断材料」の内容は、AIが提示した内容です。また、「実務では サロゲートキー + ナチュラルキーにUNIQUE制約 という「両取りハイブリッド構成」が多いです。これなら「内部的には効率的に参照できる」＋「業務的な一意性も守れる」という形になります。」との補足もAIからありました。

チームの状態

データ要件から「業務」を理解して、テーブル設計を進めますが、チームの状態も判断材料の1つになると感じてます。

経験が浅いジュニア層のメンバーが多い場合、どうしても”設計”→”レビュー”の回数が増えてしまい、予定スケジュールを超過してしまう事が経験としてありました。
本来ならジュニア層のメンバーへの”教育”も含めてのスケジュールなのですが、実際は、、、（涙）

そういった場合、後のチーム構成を変更する前提に”時間”を優先させて、「サロゲートキー」を採用して、計画的に課題解決を後ろ倒しする事もできると思います。（次は、このような判断も必要だと感じてます）

外部システムとの連携

外部システムとの連携があった場合にも、「整合性と保守性」から判断材料の1つになると感じてます。

外部システムとプライマリーキーを共有するかどうかは、重要な設計判断です。
共有するメリットとして、第一にデータの一貫性が保たれ、変換処理が不要になります。第二に、データ同期や連携が簡単になり、エラーも減ります。第三に、システム移行や外部連携がスムーズになります。逆に、内部専用キーを使うと、マッピングや変換のコストが増え、システム連携が複雑になります。

「実務では サロゲートキー + ナチュラルキーにUNIQUE制約 という「両取りハイブリッド構成」

「内部サロゲート＋外部ナチュラルUNIQUE」というハイブリッドにすると、ナチュラルキーをそのままPKにした場合と比べて 項目が増えると質問があったので、なぜわざわざサロゲートキーを置くのか？のメリットを記載します。

パフォーマンスのメリット

サロゲートキー（数値型）は 短く固定長 → インデックス効率が良い、JOINが速い。
ナチュラルキーは往々にして、複合キー（例：COMPANY_ID + EMPLOYEE_NO）になりがちで、インデックスが大きくなり、JOIN・検索が遅くなるため、頻繁にJOINや外部参照が発生するシステムでは、数値型サロゲートの方が圧倒的に有利。

変更耐性のメリット

「業務的に一意」だと思っていたキーが変わるケースは珍しくない。ナチュラルキーをPKにしてしまうと、変更時に全外部キーを巻き込んだ大改修 が必要だが、サロゲートをPKにしておけば、ナチュラルキーの変更は UNIQUE制約の更新だけ で済む。

外部キー簡素化のメリット

ナチュラルキーをPKにすると、そのまま外部キーにも伝播する（文字列や複合キーがFKとして各テーブルに散らばる
ストレージコスト増、SQLが複雑化など）。しかし、サロゲートキーなら、全てのFKが整数1カラムで済むため、大規模システムでは「FKの軽さ」が効いてくる。

おわりに

少ない経験からですが、判断材料を記載しました。パッと答えが出るはないので、ある程度設計が進んだ段階で上記の内容を元にチームで話し合って、決める事が大事だと思います。
テーブル設計は機能設計に大きく響くので、話し合う良い材料です！

あと、ナチュラルキーとサロゲートキーをシステムで統一も検討が必要です。統一した見栄えも大事ですが、無理な事は無理なので、「仕様」「状態」によって判断しましょう

---

参考（感謝）

• DB設計　サロゲートキーとナチュラルキー
• AIに壁打ち