なぜ、好ましくないのか

Question

ここによれば、

プライマリキーとユニークキーの違い

引用テキストプライマリキーとユニークキーの違いは 「確実に 識別する(identification) ための 主たる制約」 と 「NULL 以外の行が 一意であることを保証する(uniqueness) ための その他の制約」 という違いがある。

さらに原則的にキーの値を変更を許可するかしないかという面もある。 人間にとっては意味論的に異なるものであるが Oracle データベースにしてみれば、その内部の仕組みに大きな差はない。

例としては、ある顧客を会員番号を主キーとして管理している場合に (本籍)住所、氏名、生年月日のカラムの組み合わせが一意キーとして考えられる。

通常、この組み合わせのキーは一意である*1が変更可能であることに大きな違いがある。そして、これらの組み合わせを使ったテーブルのリレーションは好ましいものではない。
一方、主キー（会員番号）は退会処理などを特別な処理しない限りは変更されることはない。不可能ではないが原則、変更不可能という位置づけである。

とあります。なぜ好ましいものではないのでしょうか。

Answer 1

住所+生年月日+氏名はユニークたりえないから

本籍：好きな登録が可能
住所：引っ越し+変更忘れの場合がある
氏名：同姓同名はいる
生年月日：同じ場合もある
電話番号：使い回しされている
メールアドレス：一部業者は再取得可能

また、生年月日以外は可変である

よって その一時にはユニークだが長期保存されるデータとしてはユニークになり得ない

Comments

[ahodana]

生年月日の登録ミス修正も考えれば普遍はありません

Answer 2

本籍、氏名、生年月日でユニークである保障はありません。実際に近所に同姓同名の人がいて住所に何丁目までしかかいていない郵便物が誤配されることはわたしの家族にもあります。
旧姓ってありますが、結婚すれば苗字が変わることもありますし、本籍も変更できますから、たとえばある会社で入社してから今までの経歴を調べるのに本籍や氏名の変更を加味して調べるシステムにするととんでもなく複雑なものになってしまい、現実的ではありません。

Answer 3

値が変更される可能性がある項目（ユニークキー）を主キー（紐づけを行う項目）とするのは好ましくない、ということです。

例としては、ある顧客を会員番号を主キーとして管理している場合に (本籍)住所、氏名、生年月日のカラムの組み合わせが一意キーとして考えられる。

⇒さらに具体的な例をSQL文を交えて説明すると、
顧客情報テーブル と 顧客情報と紐づくお知らせテーブルがあるとします。

この時、teratailの右上のベルマークのリストを表示するためのSQLを考えた時に、
顧客情報テーブルにあるfrom_ニックネームと
顧客情報と紐づくお知らせテーブルにあるお知らせ情報を取得するとします。

もし、顧客情報テーブルのユニークキーを主キーにした場合（＝ユニークキーを「確実に 識別する(identification) ための 主たる制約」にした場合）、以下のような抽出SQLになってしまいます。

SELECT	T1.お知らせ内容
	,	T2.from_ニックネーム
FROM	お知らせテーブル T1
			LEFT JOIN 顧客情報テーブル T2 ON(
					T2.本籍				= T1.本籍
				AND	T2.住所				= T1.住所
				AND	T2.氏名				= T1.氏名
				AND	T2.生年月日			= T1.生年月日
				AND	T2.電話番号			= T1.電話番号
				AND	T2.メールアドレス	= T1.メールアドレス
			)
WHERE	T1.to_会員番号		= :No

抽出だけならパッと見問題ないように見えるかもしれませんが、問題は更新の時です。
UPDATE文を顧客情報テーブルだけでなく、
過去のお知らせテーブルのすべてのデータに対しても更新してあげる必要があります。
管理は複雑になりますが、実装する事はできなくなはないので、
NGではなく、「好ましくない」という表現になっているんだと思います。

主キーの通常の使い方をすると、以下のような抽出SQLになります。

SELECT	T1.お知らせ内容
	,	T2.from_ニックネーム
FROM	お知らせテーブル T1
			LEFT JOIN 顧客情報テーブル T2 ON(
					T2.会員番号			= T1.from_会員番号
			)
WHERE	T1.to_会員番号		= :No

住所等の変更についても、顧客情報テーブルのみで管理ができるテーブル設計になります。

Answer 4

基本的には主キーはテーブルの中から特定のレコードをコンストで
指定するためのものですから１カラムに付けた方が明示的だし特定も容易です。
マスターデータに用いるidのように「id=最初から違うためにつけたカラム」を
指定するのが論理的にも有効です。

ただマスターデータの運用方法では修正履歴を保持したい場合などもあります。
その場合idはユニークを保証するものではなくなります。
（同じテーブルに同じidが複数存在する）
運用方法としては更新日時＋idや、適当な連番+idなどのユニーク属性
を付けて管理します。
その際、日時＋idを主キーにすると、レコードの指定をするときなど管理が
しにくくなるのは自明なので、その手のテーブルにはautoincrementする
主キー専用のカラムを用意してレコードのユニークを担保します。

Comments

[aaaaaaaa]

ご回答ありがとうございます。コンストというのは、定数という認識であっておりますか。