初めに
基本情報技術者試験の勉強をしていると、必ず出てくるのがデータベースです。勉強すれば試験問題を解くことはできても、実際にデータベースを設計するとなると疑問点が出てきます。この記事では、データベースにおける「主キー」についてまとめます。
正規化の試験問題における主キー
LPI-Japan OSS-DBのサイトに載っている正規化の説明から一部流用します。
第4回 データベースの正規化 oss-db.jp
第3正規形を実施する際は「主キー以外の項目について項目同士で依存関係を持つもの(推移的関数従属)」を探します。
- 仕入先(主キー) -> 代表者名(非キー属性) -> 代表者連絡先(非キー属性)
この場合、「代表者名/代表者連絡先」をテーブルとして独立させます。試験問題ならこれで終了ですが、自身がデータベースの設計をするときはどうでしょう。そもそも「代表者名」が主キーになる可能性はあるでしょうか?上記サイトでは説明が省かれていましたが、第2正規形時点では以下のようなテーブル構成だったと考えられます。
商品テーブル
| 商品名 | 一個あたりの金額 | 備考 |
|---|
入荷テーブル
| 商品名 | 仕入先 | 入荷日 | 入荷数 |
|---|
仕入先テーブル
| 仕入先 | 住所 | 代表者名 | 代表者連絡先 |
|---|
仮に「代表者名」が初めから主キーであったら第2正規形/第3正規形のどちらでしょうか?
そもそも主キーってどうやって決めるの?
主キーを特定するには「スーパーキー」から「候補キー」を取り出し、そこから「主キー」となるものを選定しているようです。どのキーもテーブルから行を一意に特定できるキーになりますが、違いが幾つかあります。
- スーパーキー
- 一意に識別できるすべての組み合わせを指す。
- すべてなので一意に特定するために不要な属性も含まれる。
- 候補キー
- スーパーキーの中から行を一意に特定できる最低限の組み合わせを指す。
- 候補キーはテーブルによっては1つ以上存在する可能性がある。
- 主キー
- 候補キーの中から、識別子として最も適切なものと選んだ1つの候補キーを指す。
「候補キー」から「主キー」を選ぶ際の基準は設計者に委ねられているように読めます。ただ、一定の基準はあり
- 候補キーの中でNOT NULL制約かつ一意制約である(主キー制約)
- 候補キーの中でできるだけデータ量が少なく、簡潔な項目である
- 候補キーの中で更新がかからない項目である
等があげられます。
主キーの決め方
初めの説明時点で「仕入先」「商品名」「入荷日」がなぜ主キーで、「代表者名」が主キーではないのか。それは初めの状態を「1つのテーブル」とみなすと分かりやすいかもしれません。
第4回 データベースの正規化 oss-db.jp
上記のテーブルでは、一つの属性で一意に行を特定することはできません。そのため、人工キーを利用しない場合、既存のキーを組み合わせた複合主キーとなります。
- 「仕入先」は「住所」「代表者氏名」を特定できる
- 「代表者名」は「代表者連絡先」を特定できる
- 「商品名」は「1個当たりの金額」「備考」を特定できる
- 「仕入先/商品名/入荷日」は「入荷数」を特定できる
「代表者名」は「代表者連絡先」を特定できますが、「代表者名」は「仕入先」から特定できます。そのため複合主キーとしては「仕入先/商品名/入荷日」があれば、行を一意に特定できます。したがって この時点では「代表者名」は主キーとしては不適当です。
注意
ただし、実際にデータベースを設計する際はさらに考慮が必要です。例えば第3正規化を実行したことにより「仕入先」「代表者氏名」「商品名」は主キーになりますが、要件によっては
- 属性のデータが重複する
- 属性のデータが変更される
等が発生します。そのため「人工キー」を作成し、これを主キーとすることがあります。複合主キーに関しても検索時の指定が煩雑になりやすいため、「人工キー」を作成する場合があります。
例
- 「仕入先コード」は「仕入先」「住所」「代表者コード」を特定できる
- 「商品コード」は「商品名」「1個当たりの金額」「備考」を特定できる
- 「代表者コード」は「代表者名」「代表者連絡先」を特定できる
- 「入荷コード」は「仕入先コード」「商品コード」「入荷日」「入荷数」を特定できる
余談：主キーではなくて候補キー?
主キーの性質上、候補キーと比べて設計者の判断にゆだねられている部分が大きいです。そのため正規化の説明によっては主キーでなく候補キーに言い換えたり、両方を併記しているパターンが存在します。