人工キーを使うべきか自然キーを使うべきか、昔から論争になっています。
私は、一度すべて自然キーで実装して苦労したことがあったので、人工キーを使ったほうが良いのかと思い、調べてみました。
人工キーと自然キー
人工キーは主キーのうち、システム側で自動的に与えられるものです。
基本的にユーザーからは見えません。サロゲートキーとも言います。
自然キーはその逆で、ユーザーが与える主キーです。その性質上、1つのカラムで自然キーになることは少なく、複合キーになることが多いです。
人工キーを使うメリット
一般的に、よく言われる人工キーのメリットは次のようなものがあります。
- テーブル間の依存関係を薄くできる
- 複合キーを排除でき、SQLが簡潔に書ける
テーブル間の依存関係を薄くできる
依存関係については主キーの構造が変化したときを考えるとわかりやすいです。
人工キーを使った場合と自然キーを使った場合のER図を作りました。
この図で、もしテストテーブルに中間/期末フラグを設けることになった場合のことを考えます。
赤字が追加箇所です。
人工キーのみの場合は、変更はテストテーブルだけで済みますが、自然キーのみの場合は成績テーブルにも影響が発生していることがわかります。
このように、人工キーを使った場合はテーブル構造の変化が、ほかのテーブルに影響を与えにくくなり、後々の変更に強くなります。
複合キーを排除でき、SQLが簡潔に書ける
自然キーでは、どうしても単一のカラムが主キーになることが少なく、複合キーになることが多いです。
そのため外部キーも複数のカラムを書くことになり、SQL文が煩雑になりがちです。
次に先ほどのER図の成績テーブルから1行取り出すSQLを書いてみます。
-- 人工キーを使った場合
SELECT * FROM 成績
WHERE 学生ID = 000
AND テストID = 000;
-- 自然キーのみ使った場合
SELECT * FROM `成績`
WHERE `学籍番号` = 000
AND 科目 = '数学'
AND 年度 = 2021
AND 期 = '前期';
複合キーが混じると、条件式がだいぶ増えてしまいました。
単純に数行増えただけに見えますが、これが意外と馬鹿にならなかったりします。
条件を1行書き忘れるだけでも不具合になり、かつSELECT、INSERT、UPDATE・・・など、それぞれSQLを書く必要があるので割と見落としがちになります。
さらにテーブル構造に変更があった場合、前述のとおり自然キーでは影響範囲が大きくそこでまた不具合が発生しうるので、テストの工数まで考慮するとなかなか馬鹿にできない違いになってきます。
自然キーのメリット
自然キーを使うメリットは以下のようなものがあげられます。
- 結合せずに他テーブルのデータを参照できる
- 業務に無関係なカラムが増えない
結合せずに他テーブルのデータを参照できる
先ほどのER図を見てみると、自然キーのみで設計した場合は、成績テーブルにテストの科目、年度、期のデータが含まれています。
人工キーではいったん成績テーブルとテストテーブルを結合しなければ、これらのカラムは参照できません。
業務に無関係なカラムが増えない
人工キーは本来業務に利用しないデータであるので、ある意味冗長なデータといえます。
ER図の学生テーブルは、学生IDは、イコール学籍番号を表しているといえます。
ですので、人工キーでは学生IDが主キーで重複できないカラムなので、学籍番号に対しても同様にUNIQUE制約をつける必要があります。
また、テストテーブルでは科目、年度、期がテストIDという1カラムにまとまってしまっているので、変更の影響範囲が見極めにくくなる、ということもあります。
なお自然キーでは無関係なカラムがないため容量が少なくて済む、というメリットも言われたりしていますが、これはあまり気にしなくてよいかと思います。
自然キーは複合キーになりがちで、外部キーがその分増えるため、結局はあまり変わらないはずですので。
結局どっちをつかったらいいのか
私は大体以下の指針で検討します。
こんな意見もあるよって方はコメントいただけると嬉しいですー。
- 候補キーが複合キーでなく、かつ（ほぼ）不変ならそれを自然キーとして使う（ER図の学生テーブル）
- 候補キーが複合キーで、かつほかのテーブルから参照されている場合、依存関係を薄くして変更に強くするため人工キーを使う（ER図のテストテーブル）
- 候補キーが複合キーで、かつほかのテーブルから参照されていない場合、複合キーを使用する（ER図の成績テーブル）