第三章 IDリクワイアド
「なんでもかんでも "id" 追加したらいいと思うなよ！」
いんとろだくしょん
動画に紐づくタグが存在する、動画とタグの組み合わせは重複をはじきたい
解決策1 行の重複をはじくには主キーを設定すれば良いんだよ(ﾄﾞﾔｧ
CREATE TABLE MovieTags (
id
movie_id
tag_id
FOREIGN KEY (movie_id) REFERENCES Movies (id),
FOREIGN KEY (tag_id) REFERENCES Tags (id)
);
該当テーブルを SELECT してみる
SELECT * FROM MovieTags WHERE movie_id = 1;
結果
| id | movie_id | tag_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
ID の値が異なるにも関わらず、同じ関連づけを持ったレコードが２件入ってしまった。
解決策2
CREATE TABLE MovieTags (
id INT PRIMARY KEY,
movie_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
UNIQUE KEY(movie_id, tag_id),
FOREIGN KEY (movie_id) REFERENCES Movies (id),
FOREIGN KEY (tag_id) REFERENCES Tags (id)
);
movie_id と tag_id の組み合わせの重複を防げた！
UNIQUE KEY で重複を防ぐなら、そもそもなんで id が必要なんだろう？
目的
すべてのテーブルが主キー(primary key)を持つ事
主キー(primary key)は大切
- テーブルの全ての行が一意であることを保証する
- 外部キーから参照されることで、テーブルの関連付けを行う。
どの列を主キーにするかが難しい
一意であると保証できるパラメータは難しい
- 名前、名字 => 重複あり得る
- メールアドレス => 複数人でシェアしてたら？
疑似キー(idなど) : 対象領域をモデル化したテーブルに意味を持たない人工的な値を格のして主キーとする
アンチパターン: すべてのテーブルに「id」列を用いる
書籍やフレームワークの影響ですべてのテーブルには id がなくてはならないという考え普及している。
この考えは誤解である! すべてのテーブルに id 列を加えると意図に反した影響を生じる事がある
冗長なキーが作成されてしまう
同じテーブルにある別の列が「自然な」主キー (自然キー) として使え、かつ UNIQUE 制約が付与できる場合
CREATE TABLE Movies (
id INT PRIMARY KEY,
movie_id INT UNSIGNED NOT NULL,
(略)
UNIQUE KEY(movie_id),
);
重複行を許可してしまう
いんとろだくしょんの例 参照
id 以外の２列に UNIQUE制約が必要であると id 列はただの無駄な列になる。
UNIQUE 制約をつけると同時に UNIQUE INDEX も貼られるので、INDEX も一つ無駄になる。
主キーが複合キー場合、疑似キーを用いた場合では、クラスタ化インデックスの構成が変わるがパフォーマンスはほぼ変わらないようです。
http://d.hatena.ne.jp/yutakikuchi/20120129/1327810052
キーの意味がわかりにくくなる
そのまま、JOIN した際などに どの id か分からなくなってしまう。
SELECT
b.id, a.id
FROM
Bugs b
INNER JOIN
Accounts a
ON
b.assigned_to = Accounts.id;
id ってなんなんだー
bug_id, account_id ならすぐにわかる。
複合キーは使いにくい
扱いにくい理由、複合キーからID列に主キーを移動させるべきでない理由が文章からはよくわからず。。。
アンチパターンの見つけ方
- テーブルの主キーの列名「id」である
- 主キーは不要！ 疑似キーと主キーを勘違いしている
- 多対多の関連が重複してしまう
アンチパターンを用いてもよい場合
開発をシンプルにするため設定より規約の原則に従っているフレームワークがある。
ex) rails の Model は id が存在することで利用できる機能が多い
自然キーがながすぎる場合
ex) メールアドレスを主キーにしてインデックスをつけるのは非効率
解決策： 状況に応じて適切に調節する
わかりやすい列名にしよう
規約に縛られない
用いても良い例にでていますが、多くのフレームワークは id の規約を上書きして別の名前を宣言することができる。
self.primary_kdy = 'movie_id'
自然キーと複合キーの活用
下記条件のキーが有るなら、主キーにする
- 一意である事が保証できる
- NULL を許容しない
- 行の識別に使える
※ ただし長過ぎるなど扱いにくい列が上記条件を満たす場合は疑似キーを用いた方が良い。
連番じゃないと index 効率悪いしね。。。
下記条件であれば複号キーを用いる
- 交差テーブル
- 行を識別するための最適な方法が複数の属性の組み合わせである