質問

テーブルを設計するとき、私はユニークなカラムをひとつだけ用意し、それを主キーにする習慣を身につけました。 これは、要件に応じて3つの方法で実現している。

1. 自動インクリメントの整数型カラムを使用する。
2. 一意な識別子(GUID)
3. 行の識別子となる短い文字(x)または整数(またはその他の比較的小さな数値型)のカラム。

3番は、かなり小さなルックアップに使われる。主に、ユニークな静的長さの文字列コードや、年などの数値を持つ可能性のあるテーブルを読み込むのに使われるだろう。

その他のテーブルには、自動インクリメントの整数値か一意な識別子の主キーを設定します。

疑問点 :-)

最近、一貫した行識別子を持たないデータベースを扱うようになり、主キーがさまざまな列に分散している状態です。 いくつかの例を挙げます。

• 日時/文字
• 日時／整数
• datetime/varchar
• char/nvarchar/nvarchar

このような場合、有効なケースはあるのでしょうか？ 私なら、このような場合のために、常にIDまたは一意な識別子のカラムを定義しています。

また、主キーを持たないテーブルも多く存在します。 その正当な理由があるとすれば、それは何でしょうか？

なぜテーブルがそのように設計されたのかを理解しようとしているのですが、私には大混乱に見えますが、もしかしたらそれなりの理由があったのかもしれませんね。

3つ目の質問は、答えを読み解くための一助となるものです。複数のカラムを使用して複合主キーを構成する場合、この方法と代理キー/人工キーとの間に特定の利点がありますか？ 主にパフォーマンス、メンテナンス、管理などに関して考えています。

どのように解決するのですか？

私はいくつかのルールに従っています。

1. 主キーは必要最小限小さくする。数値型は文字型よりもはるかにコンパクトなフォーマットで保存されるため、数値型を推奨します。なぜなら、ほとんどの主キーは別のテーブルの外部キーとなり、また複数のインデックスで使用されるからです。キーが小さければ小さいほど、インデックスも小さくなり、キャッシュのページ数も少なくなります。
2. 主キーは絶対に変えてはいけない。主キーを更新することは、常に問題外であるべきです。なぜなら、複数のインデックスで使用されたり、外部キーとして使用される可能性が高いからです。1つの主キーを更新すると、変更の波及効果を引き起こす可能性があります。
3. ロジックモデルの主キーとして、問題の主キーを使用しないでください。例えば、パスポート番号、社会保障番号、従業員契約番号など、これらの「自然キー」は現実の状況下で変化する可能性があるからです。一貫性を維持するために、必要に応じてUNIQUE制約を追加してください。

サロゲートキーとナチュラルキーの比較については、上記のルールを参照してください。もし自然キーが小さくて変化しないのであれば、主キーとして使うことができます。ナチュラル・キーが大きく、変更される可能性がある場合は、サロゲート・キーを使用する。なぜなら、経験上、スキーマにテーブルを追加すると、必ず主キーを設定すればよかったと思うからです。