データベースの基礎【いろんなキー(主キー、候補キー、スーパーキーなど)】

この記事を書いている僕はシステムエンジニア６年目

普段はJavaでWebアプリを作ったりSQL書いたり・・・(DBAのような仕事は普段していないです)

応用情報技術者試験に合格(2回落ちましたが。。)し、データベースの分野が得意だったので、

独学でデータベーススペシャリストの勉強をして、昨年合格(これは1発合格)しました。

今回は、データベーススペシャリストの勉強を通じて、

データベーススペシャリストを目指す人はもちろん、

普通のシステムエンジニアやプログラマーの方にも抑えておいて欲しい

主キー、候補キー、スーパーキー、外部キーについてまとめてみました。

キー

関係(表)において、タプル(行)を一意に識別するための属性もしくは属性集合をキーといい、

キーには次のようなものがあります。

主キー(primary key)

主キーは関係(表)の中で一つだけ設定します。

一意制約(重複が認められない)と非ナル制約(NULLが認められない)を併せ持つもので、

候補キーの中から最もふさわしいものが選ばれます。

例えば、

商品を識別するために一意に振られるような商品番号とか、

社員を識別するために一意に振られるような社員番号などがあります。

候補キー(candidate key)

候補キーは関係(表)の中に複数存在することがあります。

主キーの候補となるキーが候補キーで、候補キーの条件は、

主キーとは異なり、NULLを許可する属性を持つ(もしくは含む)ものも許可します。

スーパーキー(super key)

関係(表)の中に、候補キーの数以上に存在し、

タプル(行)を一つに特定できるという条件(極小でなくていい)さえ満たせばスーパーキーになります。

具体的には、

候補キーに、様々な組み合わせで他の属性を付け足したものになります。

(関係(表)の、全ての属性もスーパーキーの一つになります。)

主キー、候補キー、スーパーキーの違いの例

次の表のような１人の社員に対して複数のタプル(行)を管理しているテーブルがあります。

この表では、

社員番号と連番、もしくは、社員番号と電話番号と住所の組み合わせで一意になります。

ただし、

電話番号や住所にはNULLを許容しており、逆に社員番号と連番にはNULLを許容していません。

この場合、主キー、候補キー、スーパーキーはそれぞれ以下のようになります。

◆主キー：{社員番号、連番}

◆候補キー：{社員番号、連番}

{社員番号、電話番号、住所}

※電話番号は別ユーザで同一電話番号を許可する前提(つまり候補キーとしない)で考えてください。例が悪くすみません。。。

◆スーパーキー：{社員番号、連番}

{社員番号、連番、氏名}

{社員番号、連番、氏名、性別}

・・・

{社員番号、連番、氏名、性別、電話番号、住所}

スーパーキーの中で、タプルを一意に識別できる極小の

{社員番号、連番}、{社員番号、電話番号、住所}が候補キーとなり、

さらに、

NULLを許可しない属性のみからなる候補キーの{社員番号、連番}が主キーになります。

また、主キー、候補キー、スーパーキーの関係を絵にすると、、

外部キー(foreign key)

他の関係(表)の主キー(または候補キー)を参照する項目を外部キーといいます。

例えば、

次の例のようにエンティティA、B間の関係が１対１の場合、

片方の主キーをもう片方に入れて外部キーとすることがあります。

関連が１対多の場合、関係A(１側)の主キーを関係B(多側)に入れて外部キーとします。

関係が多対多の場合、新たに関連エンティティを設けます。

これをエンティティCとおき、関係A、Bのそれぞれの主キーを関係Cに入れて外部キーとします。

おわりに

今回はデータベースのいろいろなキーについて確認しました。

特に候補キーと外部キーの多対多の関連を排除する部分は完璧にしておきましょう。

データベーススペシャリストの午後の試験では、

候補キーを抽出する問題や、

エンティティの多対多の関連を排除したりといった問題は必ず出ます。

基本的で簡単な内容ですが、

きちんと内容を理解し、使える知識として自分のものにしておきましょう。

2022/01/08追記

データベーススペシャリストの試験ではあまり出てこないですが、、、

現場ではSQLチューニング、性能改善周りが必要になるケースは多々あると思います。

最近、4億レコードほど登録したトランザクションテーブルのSQLチューニングやパーティション化の検証を行ったり、性能改善に必要なインデックス周りの知識についてまとめてみたので

もし興味ある方は是非こちらの記事も参考にしてみてください！！！

SQL Serverで数億規模のデータを扱って感じた最低限理解しておくべきインデックスの仕組み

SQL Serverでテーブルパーティション化して実行計画を前後比較してみた