« 「個性的な機能」の病理 | トップページ | 「DB構造の前にUIを決める」というアンチパターン »

2011.07.17

ナチュラルキーを主キーにしてはいけない

 定期的に複合主キーの話題が盛り上がるのは楽しい。好きな話題なので便乗しよう。

 「複合主キーを許すべきかどうか」の議論に関して私が理解できないのが、なぜか「ナチュラルキーを主キー(一次識別子)に含めてはいけない」という話とセットで語られがちな点だ。もちろん、ナチュラルキーを主キーに含めてはいけない。だめ、ゼッタイ。しかしこれは複合主キーの必要性とは無関係な議論であって、複合主キーを回避すべき理由にはならない。

■ナチュラルキーと人工キー

 ナチュラルキーについて、公開中の販売管理システムのモデルで説明しよう。まず、商品マスタの主キーは「内部商品№」である。これは、追加されるたびに自動的に発番されてセットされる項目で、ユーザの目には触れない「人工キー」だ。「Row ID」と思ってもらえばいい。

[商品] 内部商品№、品名、{品番}、...

 いっぽうユーザの目に触れる項目は、「二次識別子」とされている「品番」である。これは業務の現場で商品を識別するための「ナチュラルキー(自然キー。値が変化し得る一意キー)」に他ならない。こちらは、全レコード中で値が重複しなければ後で値を変更してもよしとされる。「二次識別子(主キー以外のユニーク制約)」であるということは、そういうことだ。商品テーブルに関係するすべてのテーブル関連は、主キーである「内部商品№」にもとづいて展開されている。ナチュラルキーである「品番」ではいかなるテーブル関連も構成されていない。

 ナチュラルキーを主キーにすると悲惨だ。値を変更する必要が生じたときには、レコードをコピーしたうえで削除して、主キーの値だけを差し替えて追加するといった煩雑なテーブル操作が必要になる。同時に、そのレコードを参照しているすべてのテーブルについて、関連レコードの品番の値をいちいち修正してやらねばならない。ようするにロクなことがない(注)。

■ナチュラルキーと複合主キーとは無関係

 つづいて、以上のナチュラルキーに関する議論と、複合主キーの必要性に関する議論とが無関係であることを示そう。次のようなモデルがあるとする。


[A] 、b、{c}


└∈[AD] a,d、g
┌∈


[D] 、e、{f}

 このモデルは、以下の関数従属性がデータ要件として存在することを示している。g=F(a,d)なんて関数従属性はシステムを複雑にするから無視すべし、などと考えてはいけない。業務システムのデータ要件なんてものはそもそも複雑なのである。

b=F(a)
c=F(a)
e=F(d)
f=F(d)
g=F(a,d)

 形式化された表現ではわかりにくいのであれば、Aが「社員テーブル」でDが「趣味テーブル」で、ADが「社員別趣味テーブル」くらいに考えておいてもらおう。その場合、それぞれの主キー{a},{d},{a,d}は{社員id}、{趣味id}、{社員id,趣味id}ということになろう。社員idも趣味idも、連番で与えられる「人工キー」と考えてもらっていい。属性項目b,g,eはそれぞれ「氏名」、「その趣味に対する好みの度合い」、「趣味名」あたりとなろう。

 このモデルでは、親側の2つのテーブルが「二次識別子」としてそれぞれ{c}、{f}を持っている。これらはそれぞれのエンティティ(管理対象)のナチュラルキー(値が変化し得る一意キー)と考えてもらえばいい。形式化された表現ではわかりにくいのであれば、「社員テーブル」のナチュラルキーは「通称」とか「源氏名」くらいに考えてもらえばいい。「趣味テーブル」のナチュラルキーは、なにやら口にしにくい趣味を指すための社員間の符牒みたいなものであろう。とにかくそれらのテーブルにもなんらかの必要があって、値が変化し得るユニーク制約が存在するとする。

 AとDがそれぞれユニーク制約{c},{f}を持っているからといって、[AD]上で{c,f}のユニーク制約を盛り込む必要はない。主キーの{a,d}によって{c,f}が重複しないことが保障されているからだ。

 つまり、このモデルのどこを見ても「ナチュラルキーを主キーにしてはいけない」というルールに抵触しているわけではない。同時に、論理的なレベルで複合主キー{a,d}が求められている。「ナチュラルキーを主キーにしてはいけない」と「複合主キーの必要性」とは互いに直交した問題である。

■「複合主キー要件」の実装パターン

 証明終わり。と言いたいがもう少し続けよう。このモデルの実装を考えると、以下の3パターンが考えられる。

Ⅰ.そのまま実装するパターン

[A] 、b、{c}

[AD] a,d、g

[D] 、e、{f}

Ⅱ.複合主キーを回避して代理キーを導入するパターン

[A] 、b、{c}

[AD] 、g、{a,d}

[D] 、e、{f}

Ⅲ.代理キーを導入して{a,d}の制約を組み込まないパターン

[A] 、b、{c}

[AD] 、g、a、d

[D] 、e、{f}

 これらのうち、適切なのはⅠとⅡだけである。Ⅲは危うい。なぜならⅢでは全レコード中で{a,d}の値が重複しないことがせいぜいプログラムによってしか手当されないため、長期間のシステム保守の過程で g=F(a,d)の関数従属性を保障できなくなる可能性があるからだ。

 具体的には、Ⅲでは[AD]にたとえば「ヤマダさんはバクチが大好き」という記録と「ヤマダさんはバクチが大嫌い」という記録が共存できてしまう。矛盾だ。本来ならば「社員と趣味」の組み合わせのひとつである「ヤマダさんとバクチ」に関する記録は1件しか存在してはいけない。趣味の話が矛盾するくらいなら問題にならないだろうが、業務データではシャレにならない。

 じつは、Ⅱの実装にも危うさはある。[AD]上のa、dがただの属性項目になってしまうため、ほうっておけば{a,d}でユニークであるような別の値の組み合わせに変更されてしまうリスクがある。とくにこの[AD]の主キー{h}を外部キーとして取り込んでいる別のテーブルが存在する場合、a、dの値が変更されると明らかに奇妙なデータ状況になる。もちろん、プログラムにがんばってもらって変更されないようにしておけばいいのだが、この神経質な配慮は代理キーを導入しなければほんらい不要なものなのである(主キーであれば、値を変更することはDBMSのレベルで禁止されるゆえ)。

 そういうわけで、複合主キーを回避するために代理キー(サロゲートキー)を導入するのであれば、関数従属性の要件に応じてユニーク制約が確実に組み込まれなければいけない。同時に、ユニーク制約が組み込まれた属性項目の値が更新されないような手当ても必要だ。これらを怠ると、使えば使うほどじわじわとデータ間の整合性が崩れててゆく致命的なDBが出来上がる。「SQLやテストが複雑になるから、複合主キーは避けたほうがいい」なんて物言いも問題外。それは「ボルトの数が多いと計算過程で(自分が)ミスをしやすくなるから、橋のボルトは少ないほうがいい」と言うようなものだ。工学建造物の設計は、開発者が楽するためではなく社会で役立つためになされるべきだ。

■結論

 代理キー(サロゲートキー)を使いたければ使えばよい。ただし、論理レベルのデータ要件を保全するために、いろいろメンドクサイ配慮を忘れてはいけない。私が特別な理由がない限り代理キーを使わないのはそのためだ。ちなみに、この議論とはまるで関係ないのだが、ナチュラルキーを主キーにしてはいけない。

本ブログでの参考記事
「複合キー」と実装用フレームワーク

(注)値が確定していて変化することがあり得ないようなナチュラルキーであれば、主キーにしても事実上問題は生じない。そういうわけでこのルールは、正確に言えば「値の体系が変更されることがあり得るようなナチュラルキーは主キーにしてはいけない」となる。そもそも、ナチュラルキーだからといって「品番」の体系などを安易に変更してしまえば、業務現場の混乱が避けられないので注意が必要だ。

|

« 「個性的な機能」の病理 | トップページ | 「DB構造の前にUIを決める」というアンチパターン »

コメント

コメントを書く



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




トラックバック


この記事へのトラックバック一覧です: ナチュラルキーを主キーにしてはいけない:

« 「個性的な機能」の病理 | トップページ | 「DB構造の前にUIを決める」というアンチパターン »