複合主キーを避けるべき理由

Database

データベース設計の話をしていて、「連番の主キーは業務上意味のないデータだから、テーブルに持たせるのはムダだ。複合主キーにするべき」という意見を聞く機会がありました。

脊髄反射で「ないわー」と思ったものの、理由を上手く説明できなかったので、改めて考えてみました。

その結果、次のような結論に至りました。

単一の連番カラムによる主キーと、複合カラムによる主キーとで迷ったら
実装をシンプルにし、業務変更の影響範囲を小さくするために、複合主キーを避ける

というわけで、調べたことや考えたことをメモしておきます。# 間違っている部分があれば、教えていただけると嬉しいです。

（2011/07/25 追記）複合主キーとサロゲートキーについては、要件やシステムに依存して多様な判断がありうると思います。にもかかわらず、「避けるべき」というタイトルにしたのは極端でした。申し訳ありません。ご指摘下さった皆さん、ありがとうございます。

※この記事の補足・訂正を書きました。http://d.hatena.ne.jp/torazuka/20110729/db

複合主キーについて

データベース設計における複合主キーについては、id:jflute さんが、DBFluteのサイトで非常に分かりやすい解説をされています。

上のページで取り上げられている2つの用語について、考えてみます。

  • ナチュラルキー
    • 上のページの言葉を借りると、「業務的にそのテーブルをユニークにするキー」
    • たとえば、「会員ID」「商品」「購入日時」の3つのナチュラルキーで行を一意に特定できるとき、これらは複合主キーになりえる
  • サロゲートキー
    • 業務的な事情とは関係なく、論理的に行を一意に特定するキー
    • たとえば、連番のカラムなど。いわゆる「業務コード」ではないことに注意

ナチュラルキーが業務に直接関係するデータである一方で、サロゲートキーは基本的にただの連番であるという、この観点は重要です。

（2011/07/25 追記）複合主キーの代替となるサロゲートキーにプライマリ制約をつけるときには、ナチュラルキーのカラムに必ずユニーク制約を付けます。「行を一意に特定する複数のナチュラルキーが論理的に持っている制約」を無視して、サロゲートキーを主キーとして採用する、ということではありません。jfluteさんからコメントで指摘を頂きました。ありがとうございます。


複合主キーのメリットとして挙げられる内容は、おおむね、この性質に根ざしています。

上のページでも推察されている「複合主キーのメリット」を、この観点から並べ変えてみると、それがよく分かります。

これらは、「業務上の意味をもたない、余分な、ただの連番」であるサロゲートキーに対して、ディスク容量や手間をさきたくない、という考えを示しています。また、

  • ユーザがDBを直接触る場合、（勝手知った業務データ項目である）ナチュラルキーは扱いやすい
  • 業務上の意味のないサロゲートキーは、誤解を招く

これらは、システムのためだけに存在するサロゲートキーが、ユーザから敬遠される様子を示しています。実際、ユーザにとって重要なのは、システム自体ではなく業務だからです。

複合主キーがもたらす複雑さ

複合主キーで実現できるような、業務で扱うデータのみをデータベースに収めるやり方は、ある意味ではシンプルとも思えます。

しかし、そうすることで失われる「別のシンプルさ」を認識した方がよいでしょう。

たとえば、次のようなことが挙げられます。

  • 業務変更時に影響が及ぶ範囲が広くなる

これについては、複合主キーの構成要素の変更と、値の変更の2つを考える必要があります。

構成要素の変更については、次の例が分かりやすいと思います。

複合キーの場合、構成するキー項目の組み合わせや構成数に、変更の可能性があるかどうかを検証します。

例えば、「大分類」と「中分類」という2つの分類コードを合わせた複合キーで商品を識別する場合、新たに「小分類」を増やしたら、このエンティティのデータ構造だけでなく、商品を参照する様々なエンティティのフォーリン・キーの数にも影響が生じます。アプリケーションの改修などの工数を考えると、運用後に大きな変更が生じないようにしなければなりません。

データ・モデルを安定させる | Think IT（シンクイット）

業務データに変更が生じて、既存の複合主キーでが行を一意に識別することができなくなる、というリスクの話です。

値の変更については、複合主キーにいわゆる「業務コード」が含まれる場合を考えるとよいでしょう。

複合主キーのテーブルを他のテーブルから参照するには、複合主キーを構成するカラムを外部キーとして指定します。このとき、複合主キーに、いわゆる「業務コード」のカラムが含まれていたらどうなるでしょうか。業務コードは、たとえば、顧客コード、取引先コード、商品コード、原材料コード・・・などなど、ビジネス要件によって決まるものです。

将来、もしコード体系を変更することになったら、主キー側も外部キー側も値を変更しなければなりません。

ナチュラルキーをPKにするということは、業務に直結していて実装上でも直感的でありながら、 直結しているがために業務の変更の影響をもろに食らうということにつながります。

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

ということです。

  • SQLの実装が複雑になる

複合主キーを使うと、「主キーに対するあらゆる操作」が複雑になります。たとえば、テーブルの結合条件や、主キーによるソートなどです。

複雑になると、頭を使うことが増えて面倒ですし、バグを作り込む可能性が上がります。WHERE句に複雑な条件を書くと、必要なテストも増えるでしょう。

「自分にとってはそれほど面倒でない、実装可能だ」と思うのは間違いで、他の人が保守開発することを常に考えなければなりません。

（2011/07/25 追記）「複合主キーを使うことでSQLはかならずしも複雑にならない」という旨の指摘を頂きました。ありがとうございます。この件については、分かりやすい例示ができたら補足（修正）記事を書きます。申し訳ありませんが、すぐには無理なので、ひとまずそういう指摘を頂いたことを追記します。

サロゲートキーの必要性

さて、ここまでは、「複合主キーを避ける」という視点から考えました。ここで、サロゲートキーの必要性を理解する、という視点で考えてみます。

そもそも、テーブルの行は、独立したインスタンスに当たります。その1つ1つを識別するために、サロゲートキーが必要です。

データベース設計の名著・『楽々ERDレッスン』の言葉を借りると、サロゲートキーは、インスタンスのアイデンティファイアを担保するための一手段ということになるでしょう。

楽々ERDレッスン (CodeZine BOOKS)

楽々ERDレッスン (CodeZine BOOKS)

この本では、アイデンティファイアの役割について次のように書かれています。

（ちなみに、「インスタンスへのアクセスパスを示す」のは業務コードの役割であり、アイデンティファイアとは別です）

こういうと、「ナチュラルキーによる複合主キーでも、行のアイデンティファイアを担保できるのではないか？」という疑問が浮かぶかもしれません。

これに対しては、「技術上可能だとしても、論理上おかしいから、そういうことはしない」というのが答えだと考えます。

たとえば、データベースの会員テーブルで同姓同名の人を管理する場合、IDを使って識別します。これは、個々の行が別々のデータを表すからです。たとえ、氏名カラムと登録日時カラムを複合キーとして使うことで行を一意に特定できるとしても、そうしないでしょう。

というわけで、「実装をシンプルにし、業務変更の影響範囲を小さくするために、複合主キーを避ける」という結論に行き着きました。

インデックスのことも考えたのですが、それはまた今度。。。

# 最後の例示は、説得力が弱いかもしれません。もう少し論理的に整理できるとよいのですが・・・