DB データのサロゲートキー

このページは、DB データのサロゲートキーについて考えるページです。

注意

このページを作ったひとが現在進行形で考えていることのため随時内容が変わることがあります。

サロゲートキーと自然キー

サロゲートキー

業務上発生するデータ (「商品コード」など) とは異なる、連番や UUID など人工的に生成された一意な値を持つキー。
複合主キーを避けるためなどの目的で代替として設ける (「サロゲートキー」=「代替キー」)
- SQL Server, Oracle: SEQUENCE
- MySQL: AUTO_INCREMENT
- PostgreSQL: serial
- などを使用する

項目	説明
利点	・簡単にデータの一意性が確保できる・業務データが変更になっても主キーや外部キーに影響がない　(業務上のコードが主キーの場合、コード体系が変更になったら主キー含め別テーブルの外部キーにも影響が出る) ・クエリ操作が楽　(SELECT (WHERE, JOIN), UPDATE, DELETE での指定。サロゲートキーの場合基本的に常に単独主キー) ・アプリケーション側の処理を統一させやすい (全テーブルが常に同じ形式の主キーを保持しているため)
留意点	・自然キー (後述) を主キーにしないことでデータが重複する (上図だと「会社コード」「商品コード」の組み合わせが重複する) 可能性が出てくるため、重複しないように別途ユニーク制約などを適切に設定する必要がある

項目

説明

利点

・簡単にデータの一意性が確保できる
・業務データが変更になっても主キーや外部キーに影響がない
　(業務上のコードが主キーの場合、コード体系が変更になったら主キー含め別テーブルの外部キーにも影響が出る)
・クエリ操作が楽
　(SELECT (WHERE, JOIN), UPDATE, DELETE での指定。サロゲートキーの場合基本的に常に単独主キー)
・アプリケーション側の処理を統一させやすい (全テーブルが常に同じ形式の主キーを保持しているため)

留意点

・自然キー (後述) を主キーにしないことでデータが重複する (上図だと「会社コード」「商品コード」の組み合わせが重複する) 可能性が出てくるため、重複しないように別途ユニーク制約などを適切に設定する必要がある

自然キー

業務上発生するデータが主キーの候補 (候補キー) や主キーの場合、そのキーは「自然キー」と呼ばれる
自然キーを主キーにする場合、主キーが複数のキーの組み合わせ (複合主キー) になる場合がある
(商品マスタの「会社コード」「商品コード」の組み合わせを主キーにするなど。取扱いが複雑になることがある)

設計時に検討すること

採用するフレームワークやライブラリが何か

サロゲートキーと自然キーのどちらを採用しても実際に使用するフレームワークやライブラリと相性が悪いと開発効率が下がる
- どちらが扱いやすいかはフレームワークやライブラリ選定時にあらかじめ検証する

開発関係者がサロゲートキーと自然キーのどちらを好んでいるか

おおむね SI 系は自然キー派、Web 系はサロゲートキー派の傾向があるため、関係者・決定者の主義などによってサロゲートキーと自然キーのどちらにするか選んだほうが軋轢が少ない
- SI 系
  - 古くからの DBA、DB スペシャリストが存在するなどで主義・慣習的に自然キー方式を採用することが多い
    (データモデリング論的にサロゲートキーは意味のないキーのため拒否感を持たれやすい)
  - 扱うシステムが SoR (Systems of Record。基幹システムや B2B システムなど業務の記録) のことが比較的多い
  - 開発手法がウォーターフォールに近いことが比較的多い (流動性が低い。上流でデータ構造を確定させる)
- Web 系
  - Ruby on Rails などの Web 系フレームワークの多くがサロゲートキーを主体にしている影響があり主義・慣習的にサロゲートキーを採用することが多い
  - 扱うシステムが SoE (Systems of Engagement。B2C の Web サービス、ゲームアプリなど) のことが比較的多い
  - 開発手法がアジャイルに近いことが比較的多い (流動性が高い。漸進的にデータ構造が変化する)
    - そもそも RDBMS 自体使っていない場合もある (NoSQL など)
- サロゲートキーと自然キーは宗教論争になりやすい

その他

連番の留意点

DB が分散されているなどで連番の一意性が保証できない構成になる場合、サロゲートキーに連番を使用できない
(UUID などで一意性を保証する必要がある)
挿入と削除を繰り返す (DELETE-INSERT) 処理が必要な場合、連番だと番号が急激に進む
- サロゲートキーの型がもし int (32bit) になっていると 2,147,483,647 で連番が枯渇する (最低限 bigint にする)
アプリケーション上で連番がユーザーに見える場合、下記のような状況が発生することがある
- URL に連番がパラメータとして付与されている場合、ユーザーに番号の推測がされやすくなる
  (見えているパラメータが 10000 の場合、10001 に書きかえてアクセスすることなどを試されやすい)
- 連番自体が自然キー化する場合がある
  (連番をゼロ埋めして見積番号などに使ってしまっている場合など。「同じ番号 + 枝番の見積書を作りたい」という要件が後から発生したら破綻する)

参考

Surrogate key - Wikipedia
Bill Karwin, 和田卓人, 和田省二, 児島修『SQLアンチパターン』オライリージャパン