この記事は私が実際に遭遇した主キーの選択ミスに関する話です。
そもそも主キーとは
こんなタイトルの記事を読みに来るんだからわかっているとは思いますが、まず主キーを解説します。
難しく言うと、主キーとは
ある、リレーションから1組のタプルを一意に識別する為の最小限のアトリビュートの組
の事です。
もっと砕くと、テーブルの中から一行を特定する為の列の事です。
ユニークインデックスとは
さて、RDBにはユニークインデックスと言うものも存在しています。
これをカラムに指定すると、行の値として重複が許されなくなります。
例えば、ユーザー登録できるシステムでEメールにユニークインデックスを指定する事で、同じEメールで複数の登録を防ぐことが出来ます。
ユニークは主キー？
さて、ユニークは重複しません。と言う事は行を一意に特定できそうです。
しかし、実際は主キーはユニークの一種ではあるものユニークは主キー(もしくはスーパーキー)であるとは限りません。
ユニークが主キーでない実例
では、ユニークが主キーになれない例を示します。
在庫管理システムを構築するとします。商品テーブルと、倉庫テーブルがあり、商品テーブルには「商品コード」・「商品名」・「価格」が、倉庫には「倉庫ID」・「商品コード」・「個数」があるとします。
勿論主キーはこの場合はそれぞれ、「商品コード」と「倉庫ID」・「商品コード」になるでしょう。
| 商品コード | 商品名 | 価格 |
|---|---|---|
| 1 | ポーション | 100 |
| 倉庫ID | 商品コード | 個数 |
|---|---|---|
| 100 | 1 | 20 |
| 101 | 1 | 15 |
1つの商品と2つの倉庫が管理されています。一見問題ありません。
実際問題これでも問題ない場合はあります。これで問題が出るケースは「商品コード」が手入力な場合です。
マスター情報を入力するページが存在し、ユーザーの手によって商品情報がコードと合わせて入力されるようなケースでは問題が出ます。
問題が出る理由は単純です。ユーザーが入力するということは修正できなければいけない、商品コードは変更の可能性があるという一点です。
つまり、商品コードはある一瞬においては確かに一意に識別できる物なのかもしれませんが、それがずっと、永遠なのかと言うとそういうわけではないというのが主キー足らしめない理由です。
このケースの問題点
では、これがどう問題なのかと言いますと、それは商品コード(主キー)の変更時です。
主キーは本来変わるべきでない物であるためこれを更新するのは面倒な上に更新時異常を引き起こし得る操作です。
今回のケースでは2通りのアプローチでこの操作は実現できそうです。
- 一時的に制約チェックを無効にする
- 新たな商品コードのデータを先に挿入する
UPDATE 商品 SET 商品コード=10 WHERE 商品コード=1;
を実行しようとしても制約エラーが発生します。倉庫テーブルで商品コード1が参照されているからです。
解決策は先に上げた通り、制約チェックを一時的にオフにして、商品コードを商品テーブル、倉庫テーブル双方変更します。
もしくは、先に商品コードだけ変更した行を商品テーブルに挿入し、倉庫テーブルを変更後に、旧商品データを削除します。
どちらも、よろしくはありません。特に後者はちょっとロジックをミスるだけで予期しないデータをでっちあげる可能性もあります。
真の解決策
では、この解決策はどうかと言うとこれも単純です。ユーザーが触ることが出来ない識別子を追加するだけで良いです。
| 識別コード | 商品コード | 商品名 | 価格 |
|---|---|---|---|
| 1 | 10 | ポーション | 100 |
| 倉庫ID | 商品識別コード | 個数 |
|---|---|---|
| 100 | 1 | 20 |
| 101 | 1 | 15 |
識別コードはユーザーが一切触る事が出来ず、内部でのみ閉じてしまいます。
ユーザーには表面上、商品コードが主キーかのようにふるまえば問題ありません。
反論
もしかしたら、以下のような反論があるかもしれません。
倉庫から、特定の商品コードの在庫を抜き出す際に内部結合が増えるではないか、と。
しかし、RDB的にはこの程度の(PKを使った)単純な内部結合は負荷のふの字にもならないような処理のはずです。
むしろ、私はそんな些細な最適化より、元の設計による保守・点検・不具合対応のリスクの方が遥かに大きいとみます。
さらに、言うならば、旧設計の場合は更新時に広範囲のロックが必要でその負荷の方が大きくなり得るのではないかとも思います。
まとめ
今回の事例の問題を防ぐにはとにかくユーザーから触れるのものは主キーになりえない事の方が多いというのを踏まえることで大丈夫かと思います。
主キーは名前のとおりとても大事なものです。決して直感やなんとなくなどで選んでよいものではありません。
ユニーク＝主キーじゃないの？ぐらいの認識であったり、直感で選ぶくらいならもういっそのことオートインクリメントやシリアルのカラムを足してそれを主キーに選択してください。
簡単な指針
これがすべてではないですが、今回の事例を避ける指針です。
- 値が変わるかもしれないなら主キーではない
- ユーザーに入力させるものは(超高確率で)主キーにはふさわしくない
- 値を使いまわすものは主キーではない
安直な主キーはシステムが複雑になるだけなので気を付けてください。
