【DB】 サロゲートキー（代理キー）の初歩
業務でDBを扱っていて、一意な値をとるカラムがあるのに別途連番を振ってそれを主キーとしているテーブルを見つけたのでなぜだろうと思いググってみると、それはサロゲートキー（代理キー）と呼ぶんだそうです。
これに対して、業務で使用するそのままのデータが入った主キーをナチュラルキーといいます。
で、どっち使えばいいの
どちらを使えばよいかネット上でも議論されているのでまとめてみます。
RDBMSの理論的にはナチュラルキーのでの設計が本質的な問題。
ナチュラルキーとサロゲートキーについての議論
IDリクワイアド
SQLアンチパターン
しかし現実的には設計スキル不足や追加要件などが原因で物理設計の仕様変更が起こりうる。
サロゲートキーをつけなかったせいで死にそうになった。
サロゲートキーには仕様変更の保守性を高める効果があるため、これを使おうというのが現場の知恵。
ネット上ではサロゲートキー派が圧倒しているように見えたのですが、「IDリクワイアド」で検索すると、現場的にもサロゲート一本はちょっとな意見もあります。
SQLアンチパターン読書会 「IDリクワイアド」に参加しました
・その(物理設計)段階までに、キーになりうるものが見つからないのはおかしな話。
■オートインクリメントの主キーだと、テストデータを作成するのが大変。。。
・オートインクリメントのキーが外部キーとして使われていたりする。。。
・開発時点では、各開発者のマシン上に構築したDB上でテストする。
→ そうなると、キー以外のカラムの値は同じなのに、主キーの値だけ各自のDBで異なるカオスな状況に。。。■データ移行で何が辛いかというと、シリアルのキーを移行するのがすごく辛い。
・移行の際に、番号は振替ないといけない場合がある。
・なので、IDリクワイアドは移行が大変。再構築したほうがいいことが多い。
今後は逆に、IDリクワイアドしろ！な意見も
SQLアンチパターンの感想の続き。
ここまでくると、トレードオフの問題ですね。
サロゲートキーを使うと変更に強くなります。よって保守性があがります。
が、論理設計上必要ないものを組み込むので設計がわかりづらくなります。サロゲートキーに頼ってモデリングが甘くなる傾向を指摘する記事もあります。設計がわかりづらい、甘いシステムは保守性が落ちます。
要は正しくサロゲートキーを使用しましょうということ。
つまり！
どっち使うかは経験によります！
もう一般論で決着する問題じゃないので、こうゆう場合はこうだけどこの要件が入ってくるとこっち、くらい繊細な判断が必要なんだと思います。
その「経験」の部分を、経験しろ、ではなく、冷静に書いた本に出会いたい。
本に出合いたいが結論じゃなんにもならんので小さくまとめ。
- サロゲートキーに頼りすぎない = モデリングはしっかり
- 一部の複合キーや一意性を恒久的に保障出来そうないナチュラルキーはサロゲートキーを使用
必要な部分だけ、ピンポイントでサロゲートキーを使っていきます。
念のためでサロゲートキーつけるとIDリクワイアドになるし、つけるべきところにつけてないと仕様変更で死ぬはめに。
が、しかし
サロゲートキーが使えない場合もあるようです。
[RDB][モデリング]生きているうちに自然キーvsサロゲートキー問題に決着を付けたい(1)
例えば「自然キーはいつ変わるかわからないから、すべてのテーブルにサロゲートキーを振るべきだ」と信じている人は、世の中に「(正当な理由をもって)サロゲートキーを振ることが不可能なシステム」があることに気付いていないだろう。
（中略）
これらはいわゆる情報系のシステムであって、基幹系からデータの供給を受けて動いているので、自前でサロゲートキーを振ることが不可能*2なのだから仕方がない。
サロゲートキーを使用した感想 （コメント欄）
サロゲートキーが問題なく使えるような要件およびテーブル
設計であれば、そもそも開発の難易度（規模ではない。）が
低いため、生産性は高いでしょうね。
メリットに複合キーを結合するより楽であるとありますが、
もちろんそれはその通りですが、
サロゲートキー設計では満たせないような業務要件がある
システムの方が圧倒的に開発難易度が高くなるため、
結果的に、楽に作れたシステムで採用されていたサロゲートキー
を過大に評価している人が多く見受けられる気がします。
が、どういう状況がサロゲートキーを使えない場合なのかよくわからずじまい。
そもそも
論理設計から入ることなんてほとんどないので、しがらみにしがらまれながら仕事する場合は、「データベース・リファクタリング」
一般論的サロゲートキー
メリット
- テーブル間の依存関係を弱める -> 仕様変更に強くなる
- 論理設計上必要ない項目
- カラムが増えるので容量を余分に食う
-> 複合キーの場合、参照側で食う容量を減らせるので場合によっては容量が減る
-> サロゲートキーでディスク容量がネックになる状況てどんな？ - カラム定義にコストがかかる
-> 仕様変更に伴うリスク・コストに比べれば微小 - 外部参照している場合、結合しないと関連が分からない
複合キーのSQLが簡単になる -> バグが減る、速くかける
デメリット
その他参考ページ
サロゲートキーと複合主キー
関連記事
記事はありませんでした
Comment
私は昔からいるいわゆる「バッチ屋」で、ソート、ループ、マッチング、リスト構造などを扱ってきた世代です。しばらく電算世界から離れていたため、最近になってDBなど触る羽目になり、さまよっているうちにこのエントリに流れ着きました。
ナチュラルキーかサロゲートキーかという問題については常に考えざるを得ない問題ですが、私見では現実世界との直接の表現となるテーブルではできるだけナチュラルキーを用い、DB内部だけで扱われるエンティティについてはサロゲートキーの方が扱いやすいと感じています。まあ社員番号などと言うものも、現実の社員という人間につけられたサロゲートキーのようなものですが。
言語の中では変数のスコープと言う概念がありますが、キーをスコープの概念で考えてみると、DBの外側にまで顔を出すならナチュラルキー、DB内部でのみ使われる識別のためのキーならサロゲートで良いのではないでしょうか。
閑話休題。DB構築に際してはモデリングが非常に重要であろうことは少しDBをいじってみてよくわかりました。モデリングをしっかりやっておかないと、後で膨大な手戻りが発生するであろうことも想像に難くありませんが、モデリングだけやっていると本当に実装できるのか自信がなくなって来ます。実装力の不足を感じます。精進あるのみです。
ところで、余計なことですが「こうゆう」ではなく、言うまでもなく正しくは「こういう」です。その昔、中高生女子が文章で自らの可愛らしさを表現するためにわざと用いたものを、社会人が普通に使うのは何とも奇異に感じます。ドキュメンテーションなどで恥をかかぬよう老婆心（老爺心？）ながら。
[…] 【【DB】 サロゲートキー（代理キー）の初歩】 そもそもサロゲートキーという言葉を知らなかった。 […]
[…] 【【DB】 サロゲートキー（代理キー）の初歩】 そもそもサロゲートキーという言葉を知らなかった。 […]