複合主キーよりサロゲートキー
複合主キー or サロゲートキー+ナチュラルキー
複合主キーはつかわず
サロゲートキー+ナチュラルキーについて覚えましょう！！
■使った方がいいのか？
・使わない方がよい
・ナチュラルキー+サロゲートキーで代用する
複合主キーをつかってしまうと、後でシステムを改善、追加するときに大変な事になってしまう。
■複合主キーはバグの原因に
複合主キーが多用されると、SQLが複雑になり、それがバグの原因となったり、業務変更が起こった場合に複雑なSQLを解析する必要があったり、DBの大幅なデザイン変更が必要になったりと、メンテナンスに多大な影響を及ぼすことがあります。
■フレームワークの対応
近年のフレームワークでも、サロゲートキーを前提として開発されているものが多く、知識としても必要とされることがあります
CONTENTS 10.まとめ |
■用語
ナチュラルキー...普通のカラム
サロゲートキー...id (int型+自動連番）
人工キー...
主キー...PKの事
1.ナチュラルキーとは？
ナチュラルキー(大事）
そのテーブルをユニークにするキーを、ナチュラルキー(自然キー)と呼ぶ。
・会員ID、商品、購入日時など
パット見て分かる。
2.サロゲートキーとは？
サロゲートキーとは？？（大事）
サロゲートキーは、データベースにおいて一意な値を表すために使用されるキーのこと。通常はidで表示し、数値型(int型)の自動採番された値を指します。
【int型のidカラム、AUTO INCREMENT】をつけるって事です。
➡COUNT関数を使えて便利
■事例
| id | staff_cord (社員コード) |
staff_name (社員名) |
|---|---|---|
| 1 | syain01 | 〇〇 太郎 |
| 2 | syain11 | 〇〇 次郎 |
| 3 | syain12 | 〇〇 三郎 |
| 4 | syain20 | 〇〇 カツオ |
| 5 | syain37 | 〇〇 花子 |
idをみればすぐに理解が出来ます。
分かりやすいです。
■メリット
・ミスが発見しやすい。
連番の場合はバグがや運用ミスがなければ、確実にレコードごとに異なる値が振られます。
・主キーにもなる
サロゲートキーは、単一のフィールドによる一意性を確保できる。データを保持するテーブルで、「主キー」として機能もできる。
・数値の自動連番 ・int型 のAUTO INCREMENT ・ミスが分かりやすい |
■設定について
1.外部キーとなる項目は親テーブル名＋”_id”というルール
2.サロゲートキーの採番は、自動インクリメントでもよい、
・個人的にはUUID（phpで発行）する場合もある。UUIDにすると、他のテーブルのキーと比較しても一意を保証される。
3.クラスター化インデックスは、サロゲートキーではなく
業務上で塊になっていてほしい項目を用いたインデックスにした方が良いかもしれません。
上の図をみてください。こんな感じで、変更が短くなります。
■注意点
複合キーの場合、構成するキー項目の組み合わせや構成数に、変更の可能性があるかどうかを検証します。
例えば
「大分類」と「中分類」という2つの分類コードを合わせた複合キーで商品を識別する場合、新たに「小分類」を増やしたら、エンティティのデータ構造と、商品を参照する様々なエンティティのフォーリン・キーの数にも影響が生じます。
アプリケーションの改修などの工数を考えると、運用後に大きな変更が生じないようにしなければなりません。
・複合主キーは使わない ・テーブルにidをふる ・複合主キーは後で改修が複雑になる |
【参照】
3.複合主キーのデメリット
複雑になりすぎる
複合主キーを使うと、「主キーに対するあらゆる操作」が複雑になります。
たとえば、テーブルの結合条件や、主キーによるソートなどです。
複雑になると、頭を使うことが増えて面倒です.バグを作り込む可能性が上がります。
WHERE句に複雑な条件を書くと、必要なテストも増えます。
「自分にとってはそれほど面倒でない、実装可能だ」と思うのは間違いで、他の人が保守開発することを常に考えなければなりません。
■サロゲートキー(id)は必要
テーブルの行は、独立したインスタンスに当たります。その1つ1つを識別するために、サロゲートキーが必要です。
■サロゲートキーの目的は？
複合主キーの排除
テーブル間の依存を薄める
後で変更しやすい
カラムの追加は、プログラムの変更部分が大きく。特にSQLの変更が煩雑になります。
idを振っておけば、かなり楽に変更ができます。
以上の事から、必ずサロゲートキーを設定しておきましょう。
・複合主キーの排除 ・テーブル間の依存を薄める ・後で改善がしやすくなる |
4.どうしたら？？
1.連番カラムを用意する
2.連番をPKにする
3.ナチュラルキーには、別途ユニーク制約をつける
■何をするのか？
複合主キーを使わない。
サロゲートキーをテーブルに追加。ナチュラルキーに複合でユニーク制約を貼る！！
5.ナチュラルキーの詳細
入力データ自体をPKとした場合、PKはナチュラルキーとなります。
・joinの連続となり、面倒
staff_cord (社員コード) |
staff_name (社員名) |
|---|---|
| syain01 | 〇〇 太郎 |
| syain11 | 〇〇 次郎 |
| syain12 | 〇〇 三郎 |
| syain20 | 〇〇 カツオ |
| syain37 | 〇〇 花子 |
今はidをつける手法が当たり前です
6.サロゲートキーの詳細
id部分の事。
オートインクリメントで連番をふる（カラムはint型）
でユニークになる連番ができる
| id | staff_cord (社員コード) |
staff_name (社員名) |
|---|---|---|
| 1 | syain01 | 〇〇 太郎 |
| 2 | syain11 | 〇〇 次郎 |
| 3 | syain12 | 〇〇 三郎 |
| 4 | syain20 | 〇〇 カツオ |
| 5 | syain37 | 〇〇 花子 |
■解説
普通は社員コードと社名だけで、テーブルのカラムとしては、OKです。そこにあえて、idをつくり、PKにしています。
普通に見ると意味がない数字だけれど、業務上はとても意味がある。
ナチュラルキーを複合主キーとして使った場合、主キーに対するあらゆる操作が複雑になります。
サロゲートキーの場合、1項目でレコードを一意に特定することが出来るという点で、SQLを簡潔に記載することが出来ます。
【メリット】
・テーブル間の依存が薄くなる。
・後で変更、追加しやすい
・可読性がよい
いまはサロゲートキーが主流です！！（2018年で）
■ここが良い
削除や更新、全ての処理を「ID」で書ける。
全てのテーブルでサロゲートキーとしてNoを持つと、
必要なキーはNoのみで統一されます。
画面間で引継ぐ情報や実装を統一することが可能。
7.ユニーク制約
カラムに一意性をつける
複数のカラムの組み合わせにも利用できる
■特徴
一意キー(UNIQUE)は一つのテーブルにいくつでも設定できる。
nullがOK
中間テーブルなどに制約をつけると良い
■ユニーク制約のメリット！！NULLがOK
UNIQUE 制約の場合は値として NULL を格納することができる。
複数のカラムに NULL を格納できる。
■構文
create table staff(id int unique, name varchar(10)); |
UNIQUE 制約が設定された id カラムには、自動でインデックスが作成されており、Key カラムの値に UNI と設定されています。
連番は自分でつける
Q、オートインクリメントつけると、主キーに勝手になってしまったが、これでいいのか？
Q、どの程度の長さにしたらいいのか？
int(50）??
8. 2つのカラムにユニーク制約を
複数のカラムに制約をつける事ができる。
joinyとidの組み合わせで、一意性を保つことができる。
■構文
create table staff( id int,company_id varchar(10) ,name varchar(10), unique id_company_id_index (id,company_id)); |
■後からつける構文
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 UNIQUE(カラム名); |
| id | company_id |
company_name |
|---|---|---|
| 1 | makio_zyuku | マキオ塾 |
| 2 | madao_zyuku | マダオ塾 |
| 3 | itto_kobetu | itto個別 |
| 4 | meiko_gizyuku | 明光義塾 |
9. phpmyadminで設定
複合でユニーク制約をつくっていきます。
1.3つのカラムを選びます。
2.赤枠内を「3」にしてください。
3.インデックスを作成する「実行」を押します。
3つ入っているのが分かります。
あとはインデックス名を付けたら、複合カラムでユニーク制約が作れます。
10.サロゲートキーと複合キー
複合主キーとサロゲートキーの事例
SELECT 製品名称,親部品名称,部品名称,必要数量 from 製品マスタ_t
inner join 構成マスタ_t on 製品マスタ_t.製品番号 = 構成マスタ_t.製品番号 and 製品マスタ_t .製品型式 = 構成マスタ_t.製品型式
inner join 部品マスタ_t on 構成マスタ_t.部品番号 = 部品マスタ_t.部品番号 where 製品マスタ_t.製品番号= 'NNN-NNNNN' and 製品マスタ_t.製品型式 = 'X' |
この書き方だと、分かりずらいので
構成マスタテーブルのカラム名を
製品番号➡製品_id
部品型式➡部品_id
に変更をしています。
■サロゲートキーを利用
こうすると
SELECT 製品名称,親部品名称,部品名称,必要数量 from 製品マスタ_t inner join 構成マスタ_t on 製品マスタ_t.id = 構成マスタ_t.製品_id
inner join 部品マスタ_t on 構成マスタ_t.部品_id = 部品マスタ_t.id where 製品マスタ_t.製品番号 = 'NNN-NNNNN' and 製品マスタ_t.製品型式 = 'X'; |
JOINの部分が全てidで結合できている。
usingは使えないのか？？
■攻撃に注意
idが連番であることを利用した攻撃（ID Guessing攻撃）があるらしい。
■外部キーの基本
こんな感じで外部キーが貼れます。
お互いのテーブルのカラム名が違っていても大丈夫です。
サロゲートキーの場合、「id」
10.まとめ
複合主キーを使わないのが今時らしい。
時代と共に、設計の手法は変わっていくわけですね。勉強になりました。
基本的に、何が正解か？という問題ではない。業務上の変更に耐えられるか？が大事になる。
MYSQLの学習
MYSQLの学習
DBに格納、取り出しをしてくれる部分。データの型を決めるところがまずハマル。この順番で学習できます。
■ 基本編 ■
■基本操作■
データを追加する(INSERT)
更新する (UPDATE)
削除について(DALETE)
CURDについて
■基本の操作02■
NOT NULL設定
ビューとは何か？
アップデートについて
■最新
外部結合
結合したテーブルへのupdate
結合したテーブルへのinsert
■データの置き換え
この記事を読んだ人は、こんな記事も読んでいます
SEO対策の基本 外部要因とは？
SEO対策の基本的である、外部要因とは何か？
ＳＥＯ対策をするなら絶対に知っておきたい、基本知識です。
売れるサイトを作るのに知って起きたい知識
サーチエンジンマーケティングという言葉をご存知でしょうか？
ホームページで商品やサービスを売る時に知っておきたいマーケティング
についてご紹介いたします。
SEO対策をする5つのメリットとは？
なぜ上位表示させることが大事なのか？なぜSEO対策をする必要があるのか
についてご紹介させていただきます。
SEO対策の内部要因とは？
上位表示させるためには、内部要因について知っておくことも大事です。
キーワードを上位表示させるために必要なのは、ソースコードを最適化することです
内部要因の対策について知りたい方はこちらへ
キーワード対策をする前に
そのキーワード対策。ちょっと待ってください。
そのまま対策をすると、必ずお金を損してしまいます。個人事業主や中小企業は
そんな大きなキーワードで対策をしてはいけません。
広告費用が高くなる本当の理由とは？
売れないサイトに集めていませんか？広告費用がかかるわりには
お問い合わせが少ない・・・と感じる方は多いはずです。
購入率の悪いサイトに集客をしている可能性があります。
