« ボトルネックは「業務系スキル」 | トップページ | ユニーク制約の使いどころ »

2014.08.26

主キーはインデックスではない

 仕事柄、奇妙なDB構造を目にすることが多い。どういう発想からそんな設計がされるのかを理解したいと思っていたのだが、モデラー仲間の秋里さんが先日うまい指摘をした。「主キーをインデックスみたいなものと勘違いしているからではないでしょうか」。インデックス(キー)というのは、レコードの並び順を規定するキーのことだ。

 たしかに思い当たる節がある。「こんな順にレコードが並んでいれば処理上都合がよさそうだ」という考えで主キーが設定される。さらに主キーはユニーク制約でもあるので、重複が起こらないように「多め」に項目を突っ込んでおく。つまり「ユニーク制約をともなう代表的インデックス」程度に主キーが理解された結果として、グダグダなDB構造が出来上がるのではないか。

 じっさい、昔こんなことがあった。{a,b,c,d}の複合主キーをもつテーブルXがある。ところが、別のテーブルYからテーブルXの特定レコードにアクセスする処理を見ると、{a,d}の参照キーでロジックが書かれている。bとcが抜けているのでバグではないかと問うと、「aとdの組み合わせでレコードは重複しないので、大丈夫だと思います」と説明された。「大丈夫って...では、なぜXの主キーにわざわざbとcが組み込まれているのでしょう」と問うと、「私が設計したわけではないんですが、その順に一覧したいからじゃないですかね」との返答だった。

 死んだ子の年を数えるような真似だが、この事例での設計者の発想を想像してみたい。最初にテーブルXの主キーは{a,b,c}として構想されたのではないか。その順でレコードを並べることが多いからだ。それでテストしてみると、レコードの重複が生じることがわかって、主キーにdを組み込んだ。その後、そのテーブルXを参照すべきテーブルYがあったのだが、その上にbとcが載っていないことに気付いた。けれども、調べてみると{a,d}だけでユニークになることがわかったので、参照キーをそのようにしても問題がないことがわかった。その夜、自分の設計もなかなかイケてるじゃんと満足してビールを飲んだ。

 こういう行き当たりばったりな発想で設計されたDBは少なくないような気がする。その発想の中心にあるのは「プロセス指向」だ。つまり、データ処理の過程を支援するために主キーが設定される。したがってプログラムを構想する過程で、処理されるテーブルの主キーが「進化」する。そして、主キーに関するここらへんのビミョーな事情が文書化されることもないだろうから、設計者がプロジェクトから離れたとたんに保守性が低下してゆく。あとに残るのは設計意図が不明な巨大DBだ。

 ぜひとも理解しておいてほしいのだが、テーブルの主キーは、そのテーブルのレゾン・デートル(存在理由)といっていいほどに本質的な属性である。それはレコードの並び順を暗黙的に規定しもするが、これはDBMSの仕様レベルの些末な問題でしかない。主キーを設計することの重大さは他にある。

 そのことを理解してもらうために、私はセミナーで次の話をする。あるテーブルが{a,b}を主キー(候補キー)として、c,dを属性項目として持つとする。このデータ構造において、以下の2種類の関数従属性が成立する(*1)。

{a,b}→c
{a,b}→d

 大事な点は、これら以外の関数従属性が「成立してはいけない」という点だ。具体的には、以下のいかなる関数従属性も成立してはいけない。たった4個の項目しか含まれていないテーブルなのに、そのデータ構造によって26種類もの関数従属性が「禁止される」のである。

a→b, a→c, a→d
b→a, b→c, b→d
c→a, c→b, c→d
d→a, d→b, d→c
{a,c}→b, {a,c}→d
{a,d}→b, {a,d}→c
{b,c}→a, {b,c}→d
{b,d}→a, {b,d}→c
{c,d}→a, {c,d}→b
{a,b,c}→d, {a,b,d}→c
{b,c,d}→a, {a,c,d}→b

 つまり主キー設計というものは、「いくつかの関数従属性が成立することを分析する仕事」というよりは、むしろ「圧倒的多数の関数従属性が成立しないことを保証する仕事」である。その責任の重大さがおわかりだろうか。

 なお、ここで問題にされている関数従属性はテーブルの静的な側面で、いっぽう、レコードがどのように処理されるか(たとえば読取順など)は動的な側面だ。後者は主キー設計において考慮される必要がない。そしてテーブルは「処理過程でのデータの一時保管場所」ではない。システム要件にもとづいてエンジニアリングされた「関数従属性の制約の束」をデータ構造に写像した結果である。

 ようするに、DB設計というものは専門的な作業であって、局面毎に検討課題を切り分けるような工夫なしでは完遂できない。主キーとインデックスとは検討すべき局面が異なる課題で、これらを同時に考えればわかることもわからなくなる。レコードの並び順など、DB全体の主キーを確立したあとでゆっくり考えたらいい。

 蛇足ながら、以上説明したようなややこしい議論を扱うのが面倒だからと「全テーブルの主キーを"id"にしてしまえばいいではないか。そうすればそこらへんの問題は存在しない」などと考えてはいけない。それでは問題がなくなるのではなく、DB設計者が扱うべき重大な問題が「潜航」する。結果的に、関数従属性やユニーク制約や更新不可制約の保全を、個々のアプリが引き受ける羽目になる。その先には、モグラ叩き的なコードの保守地獄が待っている。


*1.「{a,b}→c」を「{b,a}→c」と書いても同じである。つまり{a,b}が意味するのは、これらの「組合せ」であって「順列」ではない。関数従属性の検討において、レコードの並び順は問題にされない。

|

« ボトルネックは「業務系スキル」 | トップページ | ユニーク制約の使いどころ »

コメント

主キーのカラムは通常更新不可だと考えていますが、
別のテーブルの外部キーとならない項目を主キーとする
場合、更新を許しても良いのでしょうか?
例えば、単価を取得するためのテーブル
{商品CD、適用開始日}、単価 があるとして、
各処理は、このテーブルを参照して単価を求めるのみ
の場合などです。
別途、IDの項目を追加し、商品CD+適用開始日の
ユニークキーを作成するべきなのでしょうか?

投稿: MOMO | 2016.03.28 19:32

MOMOさん

商品CDや適用開始日の値を変更する可能性があるのなら、言われるような形でいいと思います。ふつうに考えれば、商品CDの値を変更することを想定する必要はなさそうなので、
{商品CD+行番}適用開始日、単価
として、{商品CD+適用開始日}のユニークキーを組み込めばいいのではないでしょうか。

投稿: わたなべ | 2016.03.29 09:37

渡辺様:

MOMO です。
変な質問に親切な回答ありがとうございました。
やはり、別途、更新が発生しないような主キーとなる
ように考慮すべきなんですね。

投稿: MOMO | 2016.03.30 09:51

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/87674/60211286

この記事へのトラックバック一覧です: 主キーはインデックスではない:

» 主キーを設計するということ [IT & Strategy]
主キーをその場その場でちゃちゃっと設計していませんか? こんにちは。鈴木尚です。 渡辺幸三さんは、セミナーに参加させていただいたり、書籍を購入してサインまで(!)いただいた事もあるほど、学ばせていただいています。 その渡辺さんが先日、ブログに投稿されました。 {a,b,c,d}の複合主キーをもつテーブルXがある。ところが、別のテーブルYからテーブルXの特定レコードにアクセスする処理を見ると、{a,d}の参照キーでロジックが書かれている。bとcが抜けているのでバグではないかと問うと、「aとdの組み合わ... [続きを読む]

受信: 2014.08.28 00:26

« ボトルネックは「業務系スキル」 | トップページ | ユニーク制約の使いどころ »