著者: Herouth Maoz により 記述されました。
編集者より: この文書は、メーリングリスト上での「PRIMARY KEY と UNIQUE 制約の違いとは」 に関する議論を元に作成しています。
題目: Re: [質問] PRIMARY KEY と UNIQUE
PRIMARY KEY(fields,...) と UNIQUE (fields,...) との違いは何でしょうか
- ただ名前が違うだけでしょうか?
- PRIMARY KEY が一意性をもっているのに、どうして UNIQUE という名前の
付いたキーがあるのでしょうか?プライマリキーは特定の行を識別するために使われるフィールドです。例えば、人物 を識別するための社会保証番号といったものです。
単に UNIQUE と指定したフィールドの組み合わせは行の識別とは関係がありません。 それは単なる整合性制約です。例えば、私がリンク集をもっていたとします。 それぞれのリンク集はプライマリキーである、ユニークな番号で識別するこ とができます。このキーはリレーションのために使われます。
しかし、私のアプリケーションではそれぞれのリンク集が一意の名前を持 つことが必要となります。なぜでしょう? そのリンク集を編集したい人間 が識別できるようにするためです。「生命科学」と名付けられた 2 つのリン ク集がある時に、必要なものは 24433 という番号がついたリンク集であり、 29882 という番号がついたリンク集ではないことが分かることはかなり難しい ことです。
従ってユーザはその名前でリンク集を選択することになります。私たちはそ れ故にデータベースの中でその名前が一意であることを確実にします。しか し、その名前を使ってリンク集テーブルと他のテーブルとのリレーションを 持たせることはしません。効率がひどく悪いからです。
更にいうと、リンク集名は一意であるにも関わらず、リンク集自体を定義し てはいません! 例えば、誰かが「生命科学」から「生物学」へ名前を変えた としても、それは同じリンク集であり、ただ名前が変わっただけでなのです。 一意である限り、名前を変更することは問題がありません。
ここまでをまとめると下のようになります。
プライマリキーとは:
行を識別し、行にリレーションを持たせるものです。
更新するのは不可能(又は困難)です。
NULLを許すべきではありません。
一意なフィールドとは:
行アクセスの代替手段として使われます。
一意である限り更新可能です。
NULL が許されます。
標準 SQL 文法上、なぜ一意でないキーが明示的に定義 されていないのかについて説明します。 まずはインデックスは実装に依存していることを理解しなくてはいけません。 SQL 文法は実装を定義してはいません。単にデータベー ス内のデータ間のリレーションを定義しているだけです。Postgres は非一意なインデックスを許していますが、SQL キーを使わなければならないインデックスは必ず一意です。
このため、インデックスを付けていなくてもカラムの組み合わせによって テーブルを検索することができます。インデックスとは、良く使われる問 い合わせがより効率的に実行されるように、RDBMS が 実装した単なる補助的なものです。いくつかの RDBMS は、 主メモリ内に保持されたキーなど追加的な手法を提供しています。それら は特別なコマンドで実装されています。例えば次のようなコマンドです。
CREATE MEMSTORE ON <table> COLUMNS <cols>(これは単なる例です。実際には存在しないコマンドです。)
実際、プライマリキーまたは一意なフィールドの組み合わせを作った時に インデックスが作られるとか、そのキーによるデータ取得に際してシーケ ンシャルスキャンよりも効率的に実行される等とは、SQL 仕様のどこにも書いてありません!
ですので、一意でないフィールドの組み合わせをセカンダリキーとして使用し たい場合、何も指定する必要はありません。その組み合わせによるデータ取得 を行えば良いのです。しかし、そのデータ取得を効率的に行いたければ RDBMS 提供者が用意した方法に頼らなければなり ません。- その方法はインデックスかもしれません。私が想像した上の MEMSTORE コマンドであるかもしれません。または、あなたの知識ではなく、特定なキーの組 み合わせの問い合わせを多く送ったという事実から知的な RDBMS が (経験から学び) 作成するインデックスなのかもしれません。