サロゲートキーのメリデリ整理

サロゲートキーとは

RDBMSにおける代理キー。システムが自動的に付与する連番を主キーとする。
対してナチュラルキーは、業務モデルを分析する過程で見出される、データを一意に判別できるキー。

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

• 不変の連番を振ることで、ナチュラルキーの主キーが実は主キーでなかった（一意でなかった）事態を防ぐことができる。このメリットが強い...
• SQLを書くとき、楽に条件を記述できる
• Webフレームワークによっては、モデルに自動採番IDを定義しないとサポートされない機能がある
• MySQLのInnoDBで、セカンダリインデックスが持つ主キーの容量を節約できる

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

• SQLチューニングの際に邪魔になる
  • ナチュラルキーを含んだ複合キーのインデックスを貼っても、サロゲートキーが選択されがち
  • covering indexさせにくい
• 正規化・データモデリングが正確でなくても主キーを持ててしまう
• 複数データのバルクインサート直後、登録したデータを特定できない場合がある（IDで参照できない）
• MySQLのduplicate On updateで、ユニークキーが一緒にあると、更新対象が不定になるリスクがある（参考：https://linkers.hatenablog.com/entry/2022/03/09/131414）
• MySQLのinsert igonore, Postgresのconflict do notingを使う時、別でユニークキーがないと、べき等性が保てない

個人的には

サロゲートキーは性能の足枷になりやすいため、好きでない。
けど、サロゲートキーの不変の主キーというメリットは大きいと認めざるを得ない
世の中的にもサロゲートキーで良いよという言説が蔓延っている...。

しかし、次のようなテーブルを作成する場合はどうだろう。

{Aテーブルのサロゲートキー, Bテーブルのサロゲートキー, 属性値}

新しくこのテーブルにまでサロゲートキーを振る必要はないよね？
AとBの組み合わせでユニークになることが見通せたら、
後からそれが変わる可能性はほぼないんじゃないかなぁ。

このようにしてささやかにレジスタンスしていきたい。

→ 正規化した結果でこれなら、中間テーブルでした。中間テーブルって、SQLアンチパターンの「リクワイアドID」でもサロゲートキーは推奨されないと記されています。