Hatena::ブログ(Diary)

虎塚 このページをアンテナに追加 RSSフィード Twitter

2011-07-13

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

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

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

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

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

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

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

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

複合主キーについて

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

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

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

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

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


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

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

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

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

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

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

f:id:torazuka:20110714082231j:image

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ということです。

  • SQLの実装が複雑になる

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

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

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

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

サロゲートキーの必要性

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

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

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

楽々ERDレッスン (CodeZine BOOKS)

楽々ERDレッスン (CodeZine BOOKS)

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

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

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

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

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

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

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

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

habuakihirohabuakihiro 2011/07/14 16:12 ERDレッスンの著者でございます。拙著をご紹介頂いてありがとうございます。

個人的にはサロゲートキーという表現は嫌い(これは単に好き嫌いの問題です)であくまでもIDなのですが、ID導入と「複合ユニークキーの導入」は両立出来るという当たり前のことが前提になってるということを申し添えさせて頂ければと思います。

あまりにも多くの方が、PK制約以外にユニーク制約を付けるということを(DB設計を本業としてる人でさえ)忘れている、あるいはそのような機能が大半のRDBMSにあるのを知らないというところから、多くの悲劇が生まれていると感じます。

データモデリングをするならRDBMSのチューニングくらいは出来て当然というのが大前提だと思うのですが、実装を知らないモデラーや設計者が多いことを悲しく思います。

突然のコメントにて失礼いたしました。

rzprzp 2011/07/14 20:25 「SQLの実装が複雑になる」理由がよくわかりませんでした。
テーブルの結合条件やソートは要件によって決まる(結合すべきカラムで結合するし、ソートが必要なカラムでソートする)ので、復合主キーかサロゲートキーかはあまり関係がないのではないでしょうか?

ryobenryoben 2011/07/14 21:38 以前、業務でコード体系の見直しの話がありましたが、思いっきり業務で使用するコードをDBの索引にしてて、一桁増やすだけでも、複数の連携システムまで影響が広がり、莫大な工数がかかるということが、わかって、どうしたら解決するんだろーと、思って何気なく買った本を見て目から鱗が落ちたのを思い出しました。
で、そのが紹介されてる本なんですよね。そこまで一致してたので、ひびりました。
ただ、今回のケースでは、複合キーかどうかは、あまり関係ない気がしてて、業務側で使用してるキーをIDとして、使うことで、業務とシステムが依存
することが問題なのかなと思いました。

通りすがり通りすがり 2011/07/15 10:26 サロゲートキーでは、何が業務的なキーなのか?ということがわかりにくいのでは?
全体的に、論理が未熟です。どちらにもメリットデメリットがある、の域を出られてないように思われます。

torazukatorazuka 2011/07/17 00:54 >羽生さん
コメントありがとうございます。
おっしゃるとおり、ユニーク制約の必要性について書き漏らしていました。参考になりました。

まともな説明ができていない記事に、書名を出してしまい、ご迷惑をおかけしました。ごめんなさい。私自身が「実装を知らないモデラーや設計者」であり、耳が痛いです。勉強して、訂正を書こうと思います。

少なくともSQLの複雑さについて、(以下のコメント返信に書いたように)相当テキトーなことを書いてしまったので、SQLドリルを買いました。これで勉強します。


> rzpさん
コメントありがとうございます。

> テーブルの結合条件やソートは要件によって決まる(結合すべきカラムで結合するし、ソートが必要なカラムでソートする)ので、復合主キーかサロゲートキーかはあまり関係がないのではないでしょうか?

おっしゃるとおりです。ごめんなさい。

「SQLの実装が複雑」とはどういうことかを定義せずに書いたのも問題でした。が、SQLが複雑=SQLの文字数が単純に多くなる、という意味だとしても、要件次第ですね。

ご指摘ありがとうございます。後ほど訂正します。


> ryobenさん
コメントありがとうございます。
やっぱり、業務コードをIDとして扱うことの問題点を解説した本として、ERDレッスン本は鉄板なんですね。

> 今回のケースでは、複合キーかどうかは、あまり関係ない気がしてて、業務側で使用してるキーをIDとして、使うことで、業務とシステムが依存することが問題なのかなと思いました

そうですね。本文の業務コードをIDとして使うケースは、複合主キーのデメリットを説明したいがために例として出しました。しかし、このケースは、ryobenさんがご指摘のとおりの問題を持っています。それに触れなかったため、「そもそも主キーに業務コードを入れるのが問題では」という方向に、多くの方を誘導してしまいました。混乱を招く書き方をして、すみません。


> 通りすがりさん
コメントありがとうございます。

> サロゲートキーでは、何が業務的なキーなのか?ということがわかりにくいのでは?
・サロゲートキーは業務的なキーではない
・業務的なキーは論理設計の時点で明確にする
というあたりを切り分けた方がよかったと思っています。

ご指摘のとおり、「避けるべき理由」を提示できるほどの論理になっていませんでしたね。すみません。もう少し整理します。

jflutejflute 2011/07/17 17:52 こんにちは、DBFluteのドキュメントをお読み頂きありがとうございます。
非常に良い記事で頑張られているようなのでちょっとだけフォローさせて頂きます。
引用記事をリンク貼って紹介しても読まない方もいらっしゃると思うので、
重要なポイントはブログ内でしっかり書いた方が良さそうですね。

例えば、サロゲートキーは付ける時は必ず
「ナチュラルキーには別途ユニーク制約を付与」
というのが前提であるとDBFluteのドキュメントでは書いていますが、

// サロゲートキーと複合主キー - サロゲートキー (代理キー) | DBFlute
http://dbflute.sandbox.seasar.org/ja/manual/topic/dbdesign/surrogatekey.html#surrogatekey

それさえ、しっかり読み手に伝われば複合ユニーク制約のことも自然と伝わりますし、
何が業務的なキーなのかわかりにくいとか、業務的なキーを置いてけぼりにして
設計をしてしまうのでは、っていう疑問も生まれないかと思います。
(一方で、自分の方でももう少しその辺ドキュメントの方で強調するようにしました)

この手の記事を書くのはとても難しく勇気の要ることかと思います。
がんばってください。

torazukatorazuka 2011/07/25 01:29 > jfluteさん
こんにちは。コメントありがとうございます。

また、分かりやすくてためになるドキュメントをいつも公開・維持してくださって、ありがとうございます。仕事でも趣味でも、参考にさせて頂いています。

おっしゃるとおり、リンク先も読まれるものという勝手な前提をもって、書いてしまいました。以後気をつけます。(本文には、ひとまず追記をしました)

jfluteさんが書かれた「正解よりも考えるきっかけが大事」というのもまた、重要なポイントだったと実感しています(こちらも、追記をしました)。システム次第、という部分を忘れずに扱う必要がありました。

ほかの方の解説を読んで、納得した気になっていたことも、いざ自分の言葉で説明しようとすると、難しいですね。理解して、整理したいと思います。

morikazumorikazu 2011/07/27 17:10 サロゲートとナチュラルキーの考え方は賛成です。
論理構造としてはなんら問題はありません。
ただし、サロゲートキーには下記のような問題が発生する可能性があります。
サロゲートキーを主キーにした場合、連番とすることが多いですがそこが問題になります。
OLTP系ではトランザクションが短いのでさほど問題にはなりませんがDWH系の大量データをバッチで複数トランザクション追加する場合にDBのINDEXブロック競合の問題が発生する可能性があります。
b-tree形式のINDEXだと連番順にINSERTしていくと特定のブロックに複数トランザクションからIOが発生し、そのためブロック競合が起こり1つのトランザクションが完了するまで別トランザクションが待たされる形になります。
トランザクションが長ければ長いほど影響がでます。
解決方法としては業務的意味を持たないので連番ではなくランダム値にするとかの検討が必要です。

既にご存知のことだと思いますが、DB内部の動作の議論が無かったのでコメントしました。

はにはに 2012/02/05 22:30 サロゲートキーは反対派です。
具体的には、有効期間を持つマスタにおいて『後づけで有効期間の変更が行われる』と、
ID基準でしか同期できないため、トランザクションテーブル側のマスタID(外部キー)と整合性(トランの基準日とマスタの有効期間)がとれなくなることが理由です。
つまり、トランザクションから見た「マスタとのリレーションがすりかわる暗黙の要件」が満たせません。

いぬいぬ 2012/03/22 14:05 >トランザクションから見た「マスタとのリレーションがすりかわる暗黙の要件」が満たせません。

それはサロゲートキーを反対する意見には成り得ない気がします。
そもそものDB設計の考え方を変えなければいけないのでは?

DumbObjDumbObj 2013/11/20 01:32 >「会員ID」「商品」「購入日時」の3つのナチュラルキーで行を一意に特定できるとき、これらは複合主キーになりえる
複合主キーを使うのは、親子関係のケースなので、この例のでは複合主キーになりえないと思います。大分類、中分類の分類コードで商品を識別するケースも同様です。
受注見出し(受注ID)と受注明細(受注ID、行番号)や、商品(商品コード)、商品サイズ明細(商品コード、サイズコード)などが、親子関係の例です。

Michitaro NaitoMichitaro Naito 2013/12/31 10:05 どちらかと言えばサロゲートキーに賛成です。例えば部・課・事業本部・案件番号の4つを主キーとするデータがあり、そのデータをWebページから参照しようと思いますと、
?department_id=111&division_id=222&section_id=333&matter_id=444
のように4つのパラメータを毎回受け渡す必要がありますが、サロゲートキーがあれば
?id=000000000001
のように1つのパラメータで一意に識別できますので、工数と(特にジュニアプログラマの)ミスが減り、個人的には良い結果を得られることが多いと感じています。

最後になりますが、いつも興味深い記事ありがとうございます。

???????????? 2014/10/20 11:55 削除が発生するトランザクション系のテーブル間で外部キー設定をする必要がある(勤務実績と残業申請のような関係性を持つ業務テーブル)だと、サロゲートキーは無駄と云うよりも害悪以外の何物でもない。

nn 2015/01/15 15:35 勤務実績と残業申請は別物な気がする・・・

YukiYuki 2015/03/13 18:30 業務キーがUPDATE対象になりえる場合の検討も必要かと思います。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

リンク元