勉強日記

チラ裏

SQL Antipatterns ch4 ID Required

SQL 勉強メモ

pragprog.com

Conventions are goot only if they are helpful.

ID Required

  • 交差テーブルにサロゲートキーidをつけてしまい、エンティティの関連が重複してしまう

コラム: Do I Really Need a Primary Key?

  • 主キーがないと
    • 重複チェックを自前で書く必要がある
      • HAVING COUNT(*) > 1
    • 重複が見つかったらどうするの
  • 主キーのないテーブルは、タイトルのないMP3集のようなもの
    • 音楽は聴ける
    • が、聴きたいものは探せないし重複も防げない

Objective: Establish Primary Key Conventions

  • すべてのテーブルに主キーを持たせようという目的やよし
  • しかし主キーについての誤解がアンチパターンを生む
  • 主キーの選定は難しい
    • あらゆる属性値が重複しうる
      • 「名前」は教科書的な例
      • eメールアドレスや社会保障番号とて厳密にユニークではない
        • 【補】ISBNなんかは運用をしくじって一意性を欠いているらしいですね
  • ドメインモデル上意味のない人工的な値が必要に
  • 方言が激しいが自動増加整数が有用
    • ドランザクションの外側で管理
    • Abort時も巻き戻らない

Antipattern: One Size Fits All

  • 主キーとサロゲートキーとの混同
    • idなる32bitないし64bit整数の自動増加カラム=主キーという誤解
  • すべてのテーブルにidカラムを追加してしまうと...

Making a Redundant Key

Allowing Duplicate Rows

  • 交差テーブルでやらかすやつ
  • (複合)自然キーにUNIQUE制約を付けなかったためにテーブルの意味が変わってしまう
    • 関連の重複が許されてしまう
  • UNIQUE制約をつければ重複を回避できるが、サロゲートキーは冗長となる

Obscuring the Meaning of the Key

  • idでは何を特定するものなのか読み取れない
  • あるエンティティのレコードを特定するものなれば、カラム名にエンティティ名を盛り込むべきである
    • 例: bugsエンティティのIDならばbug_id

Using USING

  • JOIN ONのショートハンド的なやつ
  • 同じ名前のカラムを=で結合
  • idという名前を使ってしまうと運用できなくなる
    • PKとFKとで名前被りするから

Compound Keys Are Hard

  • 「複合キーは扱うのが大変だ」という主張
  • 数学者が2次元や3次元を扱うのを面倒くさがって数直線しか使わないようなもの
    • 世界を正しく表現できない

コラム: Special Scope for Sequences

  • 自動増加キーを使わないと、下記クエリを複数のクライアントで発行するとnext_bug_idに重複が生じうる
SELECT MAX(bug_id) + 1 AS next_bug_id FROM Bugs;
  • 自動増加キーを使い、「最後の番号」を意味する関数を呼び出すことで回避可能
    • MySQL: LAST_INSERT_ID()
    • ほかベンダー方言

How to Recognize the Antipattern

  • こんなのが聞こえてきたら注意
    • 「このテーブルに主キーは必要ないと思う」
      • おそらく「主キー」という言葉をサロゲートキーと混同している
        • 「自然キーでよくない？」という意味合いだろう
    • 「多対多の関連で重複データが得られてしまったんだが？」
      • 交差テーブルにサロゲートキーを追加し、かつ自然キー(外部キーのペア)にUNIQUE制約を付けなかったおそれがある
    • 「DB理論によると、値はルックアップテーブルに置いてIDで参照すべきとのことだが、毎度JOINしなければならないのは嫌だ」
      • 正規化理論を誤解している
        • 【補】正規化は自然キーに対して行うもの
          • さらにいうと主キーではなく「候補キー」に対して行うもの

Legitimate Uses of the Antipattern

  • CoCなフレームワークのConventionに乗っかるとき
    • とはいえ主キー名をoverrideできるのが普通
  • 主キーが長すぎる場合
    • 例: パス列挙モデル
      • バスは自然キー
      • 主キーにするには長過ぎる

Solution: Tailored to Fit

  • 主キーと自動増加整数とは独立した概念である
    • 主キーはデータ型ではなく、制約である
    • 自動増加整数はデータ型であり、主キーにせずとも使用できる
  • 柔軟でない既約に縛られぬよう

Tell It Like It Is

  • 名で体を表すよう
    • bugsエンティティのPKをbug_idという名前にする
    • 主キーと外部キーとは同じ名前する
      • 例外: 関連の性質を名前に盛り込む
        • reported_byとか
    • ISO/IEC11179で標準化されている
      • Joe Celko氏による実践: SQL Programming Style

Be Unconventional

  • CoCなフレームワークで、想定している主キー名をoverrideできたりする
  • レガシーなDB用の互換のためだけの機能と思われがち
  • 新しいプロジェクトでもわかりやすいカラム名をつけるために重要な機能である

Embrace Natural Keys and Compound Keys

  • 適切な自然キーがあるならばサロゲートキーをつける必要はない
  • 必要な例:
    • すべてのカラムに変更の可能性がある
    • 行がユニークでない
    • 当初は一意に見えたものが、後になって一意ではないとわかった
  • 複合主キーを参照する外部キーもまた複合キーとなる
    • 良し悪し
      • キーの値自体が欲しい場合はJOIN不要