SQLアンチパターン「IDリクワイアド」の再検討

本


SQLアンチパターン

SQLアンチパターン

話題のSQLアンチパターンの目次に「アンチパターン：すべてのテーブルにID列を用いる」とあるのを見て、大胆にもサロゲートキーを否定しているのかと思って読んでみたが、どうも主張がはっきりしない。論点が尽くされていないような...

「SQLアンチパターン」の主張

第3章には以下のようなことが書いてある。

「IDリクワイアド」アンチパターン

IDリクワイアドは「すべてのテーブルに"id"という列名の無意味な連番の列を追加し、PRIMARY KEY制約を付与する」というパターンのこと。

何がいけないのか
  • 自然キーにUNIQUE制約を付けないなら、自然キーの重複を許してしまう
  • 自然キーにUNIQUE制約を付与して、しかもID列を主キーにするならば、キーが冗長になってしまう(ただしキー重複を許すよりはよい)
  • idという列名では何のIDなのか分かりにくいし、結合時にUSING句が使えなくて不便
  • 複合主キーが使いにくいからという理由で、id列を主キーにするのは良くない
どうすべきか
  • idという列名を止めて、xxx_id(customer_idとか)にしましょう
  • O/Rマッパーがそれを要求している場合はアンチパターンを使ってもよい(ただし列名はxxx_idにしよう)
  • 自然キーの一意性・不変性があてにならない場合は、ID列のようなサロゲートキーを導入してもよい
  • 複合主キーは必ず排除すべきものではない。使うべきときは使いましょう

感想

■複合主キーを推す理由が書かれていない

著者は複合主キーを擁護し「複合主キーが使いにくいからという理由で、ID列を主キーにするのは良くない」と言っているが、読み返してみても何が良くないのかよくわからない。理由が書かれるべき位置には

開発者の複合キーへの拒否反応は、数学者が2次元や3次元の座標を使うことを
拒否し、物体が1次元に存在するかのようにして計算することに似ています。
これによって幾何や三角法を単純化できることは事実ですが、対象とすべき
現実世界の物体を正確に表すことはできません。

とあるが、主キー制約を複合キーからID列に移動しても「対象レコードを正確に指示する」ことはできるので、これは比喩が噛み合っていない。
そもそも開発者が複合キーに拒否反応を示す理由は、それをアプリケーションで扱うのが面倒くさいから＝余計な工数が発生するからでしょう。
実際に工数がかさんでいるのなら、拒否反応を無視してよいわけがない。

■結局、アンチパターンの追認になっていないか

「自然キーの一意性・不変性があてにならない場合、サロゲートキーを導入してよい」とあるが、「IDリクワイアド」アンチパターンを積極的に使う開発者は、もともと自然キーの一意性・不変性を信用していないのだから(そうですよね？)、このガイドは「IDリクワイアドは常時使用可能」と解釈されるのではないか。

■サロゲーキーを使う場合、過去の事実をどうやって復元するのか

サロゲートキーを外部キーにして、自然キーを他のテーブルに転記しない場合、過去の事実をどうやって復元するかが問題になる。
例えば、商品マスタの自然キーである[商品コード]を受注明細に記載せず、代わりにサロゲートキーである[商品ID]を載せた場合、受注時点の商品コードを復元できる保証がなくなる。
商品コードの不変性があてにならないと考えて商品IDを導入したのだから、受注明細と商品マスタを結合して商品コードを取得したのでは、前提とやってることが矛盾してしまうからな。
これについては、トランザクションに自然キーも転記する(ただし外部キー制約は付けない)か、商品マスタを履歴化(各行に適用開始日・終了日を付ける)するなどの処置が必要だと思うが、私はサロゲートキーを使わないので、どうするのが常識なのかわからない。
著者は「こういう場合はサロゲートキーを使っていい」とは書くが、上記の問題を含めて、サロゲートキー導入時の考慮事項をあまり検討してくれない。

■IDリクワイアドのその他の問題点

個人的には、IDリクワイアドについては「DBMS実装の事情を考えずにID列を必ず主キーにする」点が問題だと思う。
主キーなんてあってもなくてもいいものなのだから、わざわざDBMSが困るような形で付与すべきでない。
例えば

  • 主キーを勝手にclustered indexにしてしまうDBMS
  • 主キーを構成する列でなくてはパーティショニングのキーにできないDBMS

などを使うのであれば、自然キーを主キーにしておかないと、パフォーマンス問題に対する打ち手を失うことになるだろう*1。


主キーはあってもなくてもよいと書いたが、これは本書の主張ではない。著者は逆に、

  • すべてのテーブルには主キーが必要である
  • 外部キー参照されるものは主キーでなくてはならない

と考えている模様。

すべてのテーブルには、重複行を防ぎ、個々の行を識別するための主キー制約が必要です。

主キーは外部キーから参照されることで、テーブルの関連付けを行います。

主キー制約が重要になるのは、次のような必要があるときです。
・行の重複を避けたい
・クエリで個別の行を参照したい
・外部キー参照をサポートしたい

大した話ではないが、ここに挙げられているのは「主キーが必要な理由」になっていない(ぜんぶUNIQUEインデックスで実現できるから)とは言わせていただきたい。
細けえなぁと思われるかもしれないが、前述の通り主キーにはDBMSによって変な制約が付くことがあるので、ある程度注意して使わなくてはならない。
主キー重視派の著者は「このテーブルには主キーは必要ない」という開発者の発言に対して「間違っている」「勘違いしている」と書くが、その人「UNIQUEインデックスがあるから主キーは要らない」と言ってたんじゃないの、とちょっと思った。

*1:前者は自然キーでの効果的なindex range scanができない。後者は例えば「日付でパーティショニング」といったことができない