サロゲートキーVSナチュラルキー
DBエンジニアの方なら、サロゲートキー（代理キー）という言葉をご存じでしょう。これは、テーブルへの入力データにある列を主キーとせずに、システム側で独自に割り当てるキーのことです（一般的には連番が使われます）。これに対して、入力データ自体の列を主キーにする場合はナチュラルキー（自然キー）と呼びます。
サロゲートキーは、基本的には不要なものです。入力データに一意なキーが存在していればそれを主キーとして使うことで、普通は問題ありませんし、オートナンバリングの機能も長らく標準SQLには存在していなかったからです（そのため、今でも実装ごとにやり方はバラバラです）。しかし、以下のような業務要件の場合には、サロゲートキーを使うことを考えます。
- ① そもそも入力データに主キーにできる項目がなく、データが重複している場合
- ② 主キーの値が使いまわされる場合
- ③ 主キーの体系が変化する場合
①のケースは、本当はあってはならないことです。そんなデータをテーブルに投入することが間違いなので、データをクリーニングして、一意になるキー項目を作るべきです。しかし、顧客からの要望で、その最善策を実現できない悲しい局面も、長い人生の中にはありましょう。このときは、サロゲートキーが有効な次善策となります（主キーなしのテーブルを作るという大胆な仕儀に及ぶかどうかのご判断は、お任せします）。
もっとよくあるケースが②と③です。たとえば、商品コードや顧客コードでは、主キーが使いまわされることは珍しくありません。そうすると、2003年の時点では「001」番が「ガムテープ」を意味していたのに、2008年には「消しゴム」を意味するように変わっている、という事態が生じます（図10）。あるいは、市町村コードなどの場合は、「005」番のA市と「006」番のB市が合併して「005」番の「C市」になる、という大きな変化が起こります。
図10 サロゲートキーなしの場合
item_no
（商品ID） | item_name
（商品名） |
|---|
| 001 | ガムテープ |
| 002 | 糊 |
| 003 | カッターナイフ |
| 004 | 付箋 |
| 005 | ノート |
item_no
（商品ID） | item_name
（商品名） |
|---|
| 001 | 消しゴム |
| 002 | 糊 |
| 003 | カッターナイフ |
| 004 | 付箋 |
| 005 | ノート |
こういう場合、常に最新のコード体系だけを参照すればよいという要件ならそのままナチュラルキーを使用できますが、時系列にデータを参照する場合には、整合性が取れなくなります[5]。
そこで、こういうケースでは、サロゲートキーを利用することで、コードの履歴情報も整合的に扱うことが可能となります（図11）。そして、サロゲートキーを利用することの副次的なメリットとして、ナチュラルキーのときは複数の列を組み合わせて一意としなければならなかったのに対し、1列だけで簡単に管理できる、というものがあります。WHERE句での条件指定もずいぶん簡単になります。
もっとも、これはあくまでオマケの効用なので、これだけを目的にサロゲートキーを利用することは考えないでください。サロゲートキーを使えば、その保存に余計なディスク領域を消費しますし、論理的には不要な項目が付け加わることで、設計を不透明なものにするからです。必要に迫られたときだけ使うようにしましょう。
図11 サロゲートキーの場合
| srg_key（代理キー） | item_no（商品ID） | item_name（商品名） | start_year（開始時点） | end_year（終了時点） |
|---|
| 1 | 001 | ガムテープ | 2003 | 9999 |
| 2 | 002 | 糊 | 2003 | 9999 |
| 3 | 003 | カッターナイフ | 2003 | 9999 |
| 4 | 004 | 付箋 | 2003 | 9999 |
| 5 | 005 | ノート | 2003 | 9999 |
商品ID「001」番の使いまわし履歴を保存できる
| srg_key（代理キー） | item_no（商品ID） | item_name（商品名） | start_year（開始時点） | end_year（終了時点） |
|---|
| 1 | 001 | ガムテープ | 2003 | 9999 |
| 2 | 002 | 糊 | 2003 | 9999 |
| 3 | 003 | カッターナイフ | 2003 | 9999 |
| 4 | 004 | 付箋 | 2003 | 9999 |
| 5 | 005 | ノート | 2003 | 9999 |
| 6 | 001 | 消しゴム | 2003 | 9999 |
おわりに
今回は、これまでの2回分とは少し趣向を変えて、SQLのテクニックよりはテーブル設計寄りのテーマをお届けしました。
冒頭でも述べたように、テーブル設計は一義的な正解を定めるのが難しい分野です。それゆえ、最適解もかなりの程度、状況依存となります。今回紹介したモデルも、場合によって毒にも薬にもなるでしょう。しかし、それだけに、工夫の余地が大きく、エンジニアにとって腕の見せ所となる分野でもあります。本稿がみなさんの思考を整理し、創意のための素材を提供できたならば幸いです。
それでは最後に、演習問題を2つ出しておきます。解答と解説は技術評論社のサイトからダウンロードできます。
演習1．OTLTテーブルに、以下の2つの要件を満たす制約を付けてください。
要件1：都道府県コードは「01」～「47」の範囲であること
要件2：性別は「0」「1」「2」「9」のいずれかの値であること
参考資料
- Joe Celko 著『Joe Celko's SQL Programming Style』
（Morgan Kaufmann Pub、2005、ISBN978-0-12-088797-2）
単一参照テーブルについては「5.3.4.1 One True Lookup Table」を参照。セルコはこのテーブルが大嫌いで、使うべきでない理由を8つも挙げて罵倒しています。セルコのOTLTに対する批判はWeb上でも読めます。
また、サロゲートキーとナチュラルキーの対比については、「3.13 Every Table Must Have a Key to Bea Table」の解説がたいへんわかりやすく、かつ詳細です。もちろんセルコは、サロゲートキーも原則的には勧めていません。
- C.J.デイト著『データベース実践講義』
（オライリー・ジャパン、2005、ISBN978-4-87311-275-6）
正規化をはじめとする設計理論の基本的な重要性を確認するには、「第7章 データベース設計理論」を参照。本稿冒頭の引用は本書p.149より。
- ミック著『達人に学ぶ SQL徹底指南書』
（翔泳社、2008、ISBN978-4-7981-1516-0）
不幸にも列持ちテーブルで、複数の列に対して条件を記述するクエリを書かねばならなくなった方は、本書の「1-8 EXISTS述語の使い方」の「列に対する量化：オール1の行を探せ」が役に立つでしょう。
