サロゲートキーと複合主キー

DB設計におけるサロゲートキーと複合PKのポイントを、DBFluteとの関わりを交えながら話題にします。 このページは、PK制約のページからの派生です。

• PK制約における話題の中心
  • あえて対比ポイントをずらす
  • ナチュラルキー (自然キー)
  • 物理設計後のDB構造のお話
• サロゲートキー (代理キー)
  • サロゲートキー + (複合)ユニーク制約
  • サロゲートキーの主な目的
• 複合主キーを排除 (サロゲートの目的)
  • 複合主キーのメリット
  • 複合主キーのデメリット
  • 複合主キーの特徴
  • よくあるバグ "条件落ち"
• テーブル間の依存を薄く (サロゲートの目的)
  • ナチュラルキーをPKにすると
  • サロゲートキーの導入で依存減らし
• DBFluteでの扱い
  • 現場での利用パターン
  • DBFluteのポリシーから
  • サロゲートキー利用のスムーズ化
  • 複合主キーがあるDBの場合
• 正解よりも考えるきっかけが大事
• 物理設計を忘れると複合主キー！？
• ナチュラルキーPKは役割が二つ

PK制約における話題の中心

PK制約におけてよく議論される話題のメインとして、サロゲートキーと複合主キーの扱い が挙げられます。本当によく聞く話で話題が尽きません。DBFluteでも扱いも含めて分析してみたいと思います。

あえて対比ポイントをずらす

本来、"サロゲートキーとナチュラルキー" という対比で分析するのが構造的ですが、 話題として複合主キーというものに焦点があたることが多く、かつ、議論をする上での重要なポイントでもあるため、あえて "サロゲートキーと複合主キー" というテーマにしています。

ナチュラルキー (自然キー)

まずは、言葉の整理から。業務的にそのテーブルをユニークにするキーを、ナチュラルキー(自然キー)と呼びます。 例えば、MaihamaDB の購入テーブルのナチュラルキーは、"会員ID、商品、購入日時" の三つのカラムです。 後に説明するサロゲートキー(代理キー)の対になるものです。業務キー、ビジネスキーとも呼ばれます。

物理設計後のDB構造のお話

論理設計終了段階でのDB構造ではなく、実際にデータが投入され、SQLが発行されてアプリケーションが動作する物理設計後のDB構造に焦点を当てています。 逆に論理設計の時点ではサロゲートキーはない方が設計しやすいので(あるとややこしいので)、少し論点が変わります。

ただ、サロゲートキーのことを考えなかった論理設計の構造をそのまま物理的なDBの構造に反映させてしまわないように気をつける必要があります。

一方で、Webサービス系などで論理設計と物理設計をもう一緒くたにやっていくような場合は、 正規化の論理に目の前にあるサロゲートキーを含めないようにすることが大切で、モデリングはナチュラルキーを基軸に考えていくとよいでしょう。

サロゲートキー (代理キー)

サロゲートキー + (複合)ユニーク制約

ナチュラルキーをPKにせず、例えば連番となるようなカラムを用意して、それをPKにします。 これがサロゲートキーと言われるものですが、ナチュラルキーには別途ユニーク制約を付与する というのを忘れてはいけません。

ここでは、ナチュラルキーにユニーク制約を付けずにサロゲートキーだけを導入する方式は、業務的・実装的に意味はないと考え、ここでは取り扱いません。 議論の対象にすらしません。ユニーク制約を付けることで業務的なユニーク性を保ちつつサロゲートキーの恩恵を得ることができ、同時にナチュラルキーを明示することができます。

なので、厳密には "サロゲートキーを付ける" というのは...

サロゲートキー追加して、ナチュラルキーにユニーク制約を貼る という行為を指します。

サロゲートキーの主な目的

サロゲートキーの主な目的は二つとなります。

• 複合主キーを排除
• テーブル間の依存を薄く

複合主キーを排除 (サロゲートの目的)

例えば連番カラムをPKにして、ナチュラルキーのカラムに複合のユニーク制約を付与します。 これは同時に複合FKを排除することにもなり、そのメリットも享受されます。

さて、そもそも複合PKの特徴とは？

複合PKの特徴を知らないと、サロゲートキーの役割もハッキリしません。 サロゲートキーとの比較におけるメリットデメリットをまとめてみます。その逆がそのままサロゲートキーの特徴とも言えます。

複合主キーのメリット

代理キーが無い分、容量が少ない

代理キーを付けない分、ハードディスクへの負担が少なくなります。 ただし、そのテーブルを参照するテーブルがある場合は、FKカラムが複合になりますので、結局はその分の増えた容量で相殺されてしまいます。

結合なしのSQLでも業務的なデータが見られる

例えば、複合PKを持つテーブルを参照するテーブルにおいて、単純な "select * from xxx" を実行したときに、サロゲートキーだと参照しているデータが何なのかわかりませんが、 複合キーであれば、キー自体(業務的なコードや日付など)を持っているので結合せずに参照できます。

主に運用者などがデータを直接DBから閲覧する場合などに、 簡単なSQLで済むという面があります。ただし、そういった業務がアプリ化されていない証拠とも言えます。 また、アプリ化とまで言わなくても、閲覧の度にSQLを一から書いているなど、DBから閲覧方法が効率化されていない証拠でもあります。 (結合したSQLを一度書いて手元に置いておけばよい)

ただ、これに関しては、サロゲートキーを連番カラムにした場合に限ります。サロゲートキーはほとんど連番カラムが前提ですが、 厳密には連番カラムではなく、業務値を統合した値でも構いませんので、このメリットを重視する場合は、サロゲートキーを使ってもそういった工夫は可能です。 (その場合、業務値を統合した値は、あくまで人が認知しやすいユニーク値という扱いであり、 何文字目から何文字目を切り取って使う、というような実装はリレーショナルデータベースの使い方とかけ離れたものとなってしまいます)

業務と無関係のカラムがない

業務と無関係のカラムがないため、誤解や間違いが生まれにくいという面もあります。 サロゲートキーで "IDだらけ" になると、それはそれで間違い(ID違いによるバグ)が発生しやすくなるのも事実です。

サロゲートキーに対する処理が減る

DB設計者は常に忙しいものです。複合PKのままであれば、ERDツールなどでサロゲートキー用のカラム定義コストがありません。 (カラムを定義するという作業は確かに面倒なものです)

また、アプリで連番カラムを利用するには、専用の処理が必要になることがあります。 DBMS間で連番処理が統一されていないという事実もあります。 ただし、DBFluteでもそうですが、多くのO/Rマッパでは Identity やシーケンスなどの利用を透過的に行う機能が備わっています。

何がナチュラルキーなのかわかりやすい

PKがそのままナチュラルキーなのでわかりやすいと言えますが、これはサロゲートキーを付けない場合に比べてという話であり、 先述の通り、ここでは サロゲートキーを付けるなら一緒にナチュラルキーに(複合)ユニーク制約を付ける というのが大前提であるため、ERDツールがユニーク制約を図上にしっかり表示さえすれば、ほとんど実質的な差にはなりません。

複合主キーのデメリット

SQLの結合がバグの温床

複合FKとなると、結合の join 句の条件の記述量が膨大です。面倒というだけならまだ良いですが、条件落ち ならびに 条件違い というバグを潜ませる危険性があります。相関サブクエリを書くような場合でも同様です。 アプリでは、DBFluteのようなO/Rマッパを使って結合条件を自動化することも可能ですが、SQLを書く機会はアプリだけとは限りません。

this - よくあるバグ "条件落ち"

PKの複数対象値指定の検索ができない

DBFluteで言えば、InScope: where PK in ('a', 'b') です。"A, B" と "A, D" と "B, F" のレコードを検索するという場合に、InScope は利用できません。"or条件" を使って実現できますが、SQLが複雑になる可能性があります。 一部 DBMS では、複合の InScope をサポートしているものもありますが、全ての DBMS ではありません。

同じく、DBFluteで言う、InScopeRelation: where PK in (select FK from ...) も同様に利用できません。これができないとアプリでは結構つらいので、別の方法を考える必要があります。

アプリ上でのユニーク値の保持処理が煩雑

一つの値でユニークにできないため、例えばWEBアプリにおいて、GETパラメータや hidden フィールド、タグの value 値などでユニークキーを扱う際に、処理が非常に煩雑になる可能性があります。煩雑なだけならまだ良いですが、バグを引き起こす可能性もあります。

同様に、DBとは違う世界において、"複合値でようやくユニークになるもの" に対する対応ができていない(する気もない)ことがあり、DB以外のところで問題が発生する可能性があります。

コード値の構成変更などの業務変更に弱い

複合PKを持つということは、それを参照するテーブルでは複合FKを持つことになります。 業務的なナチュラルキーのカラムを参照するテーブル側でも持つことになります。 そのカラムの値構成仕様(例えば、コード値など)が変わってしまった場合、それはつまり、主キーを更新することになります。 影響範囲はサロゲートキーを利用している場合に比べて膨大です。アプリ上でのユニーク値の保持処理にも影響が出る可能性があります。

これはもちろん、単一のナチュラルキーによるPKでも同じ話になります。サロゲートキーのもう一つの目的である "テーブル間の依存を薄くする" という話に関連します。ただ、複合主キーだと、よりその影響を受けやすいと言えるでしょう。

複合主キーの特徴

さて、複合主キーの特徴が見えてきます。メリットの多くは、コストの掛からない代替策がある、もしくは、問題が深刻化しにくいものであるというところです。 また、時代の流れや周辺ツールの進化と共に、メリットの度合いは減っていく可能性があります。一方で、デメリットは問題が深刻化する可能性を十分に秘めています。

ただ、もう一つ大きな特徴があります。複合主キーのデメリットは、(あまり実装に関与しない)DB設計者にはデメリットと感じにくいということです。 特に、開発現場で組織的にDB設計者とアプリが精神的にも距離的にも離れていると、特にその傾向が顕著になる可能性があります。

極めつけの特徴は、複合主キーはSQLとも相性が悪い ということです。DB以外のレイヤで都合が悪いという話なら "そもそも自分レイヤ以外では都合が悪い" というのは一般的にありふれた話ですが、DBの話でありながらDB自身で都合が悪いという特徴があります。 こういったことから、SQLをよく書くDB設計者と、SQLをあまり書かないDB設計者で感じ方が変わるという面もあります。

そして、SQLと相性が悪いということは、当然、それを扱うO/Rマッパのようなツールでも都合が悪いものです。 また、DBアクセスレイヤを飛び越えて、データに依存する画面レイヤのフレームワークなどもその影響を受けます。

よくあるバグ "条件落ち"

複合主キーの際によくあるバグの一つに 条件落ち というものがあります。非常に単純なバグですが、非常に多く見受けられます。 例えば、SQLで結合条件を記述する際に、全てのPKを構成する全てのカラムで結合条件を記述しなければなりませんが、その一つが抜け落ちている状況を言います。

e.g. 結合条件が抜けているパターン @SQL
select ...
  from FOO_ORDER foo
    left outer join FOO_SALE sale
      on foo.FIRST_FK = sale.FIRST_PK
     and foo.SECOND_FK = sale.SECOND_PK
     and foo.FOURTH_FK = sale.FOURTH_PK
-- where is the third condition?

また、条件間違いによる条件落ちもあります。特に短い略語のエリアス名を使う場合、 そして、登場している関連テーブルに同じ名前のカラムが存在するような場合に要注意です。

e.g. 条件間違いによる条件落ちのパターン @SQL
select ...
  from FOO_BAR_ORDER fbo
    left outer join FOO_BAR_ADDTIONAL fba
      on fbo.FIRST_PK = fba.FIRST_PK
     and fbo.SECOND_PK = fba.SECOND_PK
     and fbo.THIRD_PK = fba.THIRD_PK
    left outer join FOO_BAR_SALE fbs
      on fbo.FIRST_FK = fbs.FIRST_PK
     and fbo.SECOND_FK = fbs.SECOND_PK
     and fbo.THIRD_FK = fba.THIRD_PK
     and fbo.FOURTH_FK = fbs.FOURTH_PK
-- where is the mistake?

この条件落ちは、単にディベロッパーの不注意だけで発生するものではありません。業務変更によるPK構成要素に追加があった場合、既存のSQLは全て条件落ち状態となります。 当然、業務変更とともに直すべきですが、とてもやっかいなのが DBを変更しても特にコンパイルエラーにもならなければ、SQLの実行エラーになるわけでもない 可能性があることです。つまり、影響範囲を特定するのが大変、最悪は特定できずにリリースしてしまうことです。 テストをしてもテストデータの精度次第では抜け落ちる可能性も秘めています(抜けていてもそれなりに動いてしまう可能性)。

また、単に結合条件の話に留まりません。よくある "PKによる等値条件" や "PKで並べる(ソート)" など、様々なところでPKを意識した処理が必要になりますが、同じような話が通じます。

テーブル間の依存を薄く (サロゲートの目的)

ナチュラルキーをPKにすると

複合・単一に関わらずナチュラルキーをPKにするということは、そのテーブルを参照するテーブルでは、そのカラムの値をFKカラムとして保持することになります。

もし、コード編成の見直しなどの業務が発生してそのPKであるナチュラルキーのコード値の仕様が変わるというような状況が発生した場合、 その影響はそのテーブルを参照しているテーブル全てに広がります。

また、ナチュラルキーの構成カラムが増えたり減ったりとした場合、 同様に参照しているテーブルに影響しますし、さらにはSQLの実装にも影響が出ます。 結合処理や相関サブクエリを使っている箇所で根こそぎ修正を入れなければなりませんが、やっかいなのは、構成が変わってもコンパイルエラーに なるわけでもなく、場合によっては実行してもエラーにもならず、データ次第では一見動いているように見えてしまうことです。 本番環境に反映してから修正漏れが判明するというのは想像したくもないことです。アプリを利用する側からしても良い話ではありません。

サロゲートキーの導入で依存減らし

ナチュラルキーをPKにするということは、業務に直結していて実装上でも直感的でありながら、 直結しているがために業務の変更の影響をもろに食らうということにつながります。確かに変更が発生するのは仕方のないことではありますが、 その影響は最小限にしたいものです。そのためには、テーブル間の依存関係を薄くすることが重要です。

サロゲートキーを導入することで、テーブル間の依存度合いは減り、業務変更のインパクトも(何もしない状態よりは)少ないものになります。 複合主キーの場合の方が効果は顕著ですが、単一キーでも同じ話です。例えば、ExampleDB の商品テーブルは、単一のナチュラルキーである商品ハンドルコードでユニークですが、商品IDというサロゲートキーを利用しています。 商品ハンドルコードは、業務で利用する値であり、業務変更でコード値の構成変更が発生する可能性があると想定したものです。

DBFluteでの扱い

現場での利用パターン

サロゲートキー、複合主キーに関して、実際の現場での利用パターンを大雑把に三つに捉えてみました。

A. サロゲートキーなしで複合主キーを許容 (ナチュラルキーPK)

何もしないパターンです。複合主キーのデメリットをもろに食らいます。また、業務変更のインパクトも巨大です。 よって、あまり変更が発生しにくい業務のシステムであることが求められます。

B. 全てのテーブルでサロゲートキーを利用 (サロゲートキーPK)

統一であるため、ディベロッパーにもわかりやすく、サロゲートキーのメリットを最大限享受します。 一方で、業務に関係のない ID が増えるため、若干の間違い(ID違いなど)を起こす可能性もあります。

C. 複合主キーになるもの、業務変更ありそうなものにだけサロゲートキー

A と B の折衷案です。とにかく複合主キーはサロゲートキーを使って問答無用で避け、 単一構成のナチュラルキーは業務変更ありそうなものにだけサロゲートキーを適用するというやり方です。 両方のメリットをバランス良く享受しようという発想のやり方ですが、何が業務変更ありそうなのかの判断のさじ加減が難しくDB設計者が苦労するところです。

※サロゲートキーを付けるときは、ナチュラルキーにユニーク制約を付けることを大前提にしています。 ユニーク制約なしのサロゲートキーのみの導入は選択肢にも含めていません。

DBFluteのポリシーから

DBFluteでは、もともと DB変更に強い をポリシーにしたフレームワークです。 また、安全性 もテーマにしています。そのことと、サロゲートキー、および、複合主キーの特徴を踏まえると、 当然のことながら、B および C のパターンを推奨しています。つまり、サロゲートキーの積極利用を奨めています。 やはり、少なくとも問題が深刻化しやすい複合主キーはできるだけ無い方が良い と考えられます。

もう少しざっくり言うと、"サロゲートキー+ユニーク制約" のデメリットと "複合主キー" のデメリットを天秤にかけたとき、DBだけじゃなくアプリも含めたシステム全体 で考えると、圧倒的に後者の方がつらいことが多く、前者のデメリットはフレームワークとか担保しやすくて深刻化しづらい、という感覚値を持っているからです。

サロゲートキー利用のスムーズ化

その際、サロゲートキーをスムーズに利用できるようにするために、連番カラムの自動解決 をする機能を持っています。Identity カラムはメタデータから自動判別し、シーケンスにおいても(DBMSによりますが)自動判別、そして、Entity への連番値の自動設定など行います。また、シーケンスはDBFlute自体にキャッシュ機能を備えており、シーケンス発行のオーバーヘッドを抑えます。

複合主キーがあるDBの場合

DBは長生きなものです。おいそれと変わるわけにはいかないものも多いでしょう。複合主キーを許容せざるを得ない場面もあります。 そのときのために、DBFluteでは複合主キーによる機能低下を最小限にしようと努めています。 (ただし、SQLでできないことは、やはりDBFluteでもできません)