More than 5 years have passed since last update.

サロゲートキーとナチュラルキーについて調べた

Last updated at 2018-12-14Posted at 2018-12-14

俺でもわかるシリーズアドベントカレンダー１５日目です。よろしくおねがいします。

お恥ずかしながらデータベースのナチュラルキーとサロゲートキーについて知らなかったので、自分なりに調べてみた。

はじめに

まず、テーブルの中のデータで一意となるものを主キーと呼びます。これは流石に知っていますね。
ですが、この主キーはさらに２つに分類することが出来ます。それが、サロゲートキーとナチュラルキーです。

それぞれ特徴を見てみます。

サロゲートキーは機械的につけられた連番のキーです。オートインクリメント等でつけられたものがそうです。
下のNoカラムがサロゲートキーです。

このキーがあれが、一つのレコードを特定できるため、CRUD処理がしやすくなります。
また、外部キーを使用する際にもサロゲートキーだけを考えるだけで良いので、テーブル間の依存がしにくいです。

ただ、データ自体は機械的に付けられた連番なので、このデータだけ見てもなんの情報なのか全くわかりません。
必ずなにか別のカラムと合わせて取得します。

私は主キーといえばこのことを指すものだと思っていました。

ナチュラルキーは、サロゲートキーとは全くの逆です。データの中身を見れば、なんの情報なのが人間が理解することが出来るようなカラムが主キーとなります。
とある学校で、以下のような学生の情報を持つテーブルがあったとしましょう。

さて、ナチュラルキーも主キーの一種だとはじめに書きましたが、上記の場合はそれが主キーになるでしょうか。

なんだかどれも主キーになり得なさそうです。

ここで出てくるのが複合主キーと呼ばれるものです。
例のテーブルの場合は学部番号, 入学年, 出席番号の３つをまとめれば特定の学生を見つけることができそうです。

サロゲートキーと違って、どのカラムを一つ取っても利用者にとってはなんの情報かはわかりやすいです。

ただし、特定の一人を見つけるために３つのカラムを見る必要があります。
また、外部キーを設定することになった場合は、複合外部キーとしなくてはなりません。JOINする際にはSQLの文量は、サロゲートキーを採用しているときと比較して多くなります。

機械的につけられた連番のキー。
一つのカラムで一人の特定が可能、テーブル間で依存が起こりにくい。
サロゲートキーだけでは、なんの情報だか分からない。

ユーザが設定したキー。
一つの絡むだけでも何の情報だか理解できる。
一人の特定をするのに複数のカラムを要する（複合主キー）
テーブル間の依存が強くなりがち。

どっちの方式で主キーを決めるかは永遠の終わらない議論のような気がします。
システムによって、どちらが良いかよく考える必要がありそうですね。