著者: Herouth Maoz(<herouth@oumail.openu.ac.il>)によって書かれました。もともとは1998年3月2日のユーザーメーリングリストでPRIMARY KEYとUNIQUE制約の違いは何ですか？」という、質問の答えとして書かれました。
元記事：
Subject: Re: [QUESTIONS] PRIMARY KEY | UNIQUE
What's the difference between:
PRIMARY KEY(fields,...) and
UNIQUE (fields,...)
- Is this an alias?
- If PRIMARY KEY is already unique, then why
is there another kind of key named UNIQUE?
翻訳：
件名: Re: [質問] PRIMARY KEY | UNIQUE
PRIMARY KEY(fields,...) and
UNIQUE (fields,...)
これらの違いは何ですか？
- これは別名ですか?
- もし PRIMARY KEY がすでに一意であるならば、なぜ
UNIQUEと言う別のキーがあるのですか?
主キー（primary key）とは、特定の行を認別するためのフィールドです。例えば、人物を特定する場合の社会保障番号のようなものです。
ただ単に一意な（UNIQUEな）フィールドの組み合せは、行の識別（特定）とは関係ありません。単に整合性制約なのです。たとえば、複数のリンク集を持っているとします。それぞれの集合は一意な数字で特定され、それが主キーと呼ばれ、このキーがリレーションで使われます。
しかし、アプリケーションはそれぞれの集合が一意な名前も持つことを要求します。なぜならば、その方が集合を修正する人間が容易に識別できるからです。もし「生命科学」と呼ばれる集合が2つあり、29882ではなく24433という番号の付いた方を必要とする場合、非常にわかりにくくなってしまいます。
よって、ユーザは集合を名前で検索します。したがって、データベースの中でその名前が一意であることを確認する必要があります。しかし、それ以外は、集合の名前で集合テーブルに関係するものはありません。それはあまり効率がよくありません。
さらに、一意であるにもかかわらず、集合名は実際に集合を定義するわけではありません。たとえば、もし誰かが「生命科学」を「生物学」に変えた場合、それは集合名だけが変わり、集合を構成しているものは同じままです。名前が一意でさえあればそれで大丈夫です。
上記のことから、下記のようなことが言えます。
主キーは次のような性質を持ちます。
行を識別し、関連付けるために使用
更新は不可能(もしくは難しい)
値にNULLを許可しないべき
一意なフィールドは次のような性質を持ちます。
行への代替アクセスとして使用
一意でありさえすれば、更新が可能
値にNULLが可能
なぜ標準SQL構文では非一意なキーが明白に定義されているのでしょう?まず、インデックスは実装に依存するということを理解する必要があります。 SQLは実装を定義するのではなく、単にデータベースのデータ中にあるリレーションのみを定義します。 PostgreSQLでは非一意なインデックスが作成可能ですが、 SQLキーを強制するために使われるインデックスは常に一意です。
したがって、列にインデックスが存在しなくても、テーブルのどんな組み合わせの列でも問い合わせることができます。インデックスとは単に、頻出する問い合わせをより効率よく行うためにRDBMSが提供している実装を支援するものです。 RDBMSの中には、キーをメインメモリに残すなどの付加措置を提供しているものもあり、下記の例のようなコマンドを提供しています(これは例で、実際にあるコマンドではありません)。
CREATE MEMSTORE ON table COLUMNS cols
実際に、主キーもしくはフィールドの一意な組み合わせを作成した時に、インデックスが作成されることやキーによるデータの抽出が逐次スキャンよりも効率がよくなるとは SQL規格のどこにも述べられていません。
ですから、もし、二次キーとして一意ではないフィールドの組み合わせを使いたい場合、特に何も指定する必要はありません。ただその組み合わせで抽出を始めるだけです。しかし、もし抽出を効率よく行いたい場合は、使っているRDBMS の供給者が提供する方法に頼らなくてはいけません。インデックスや、空想上のMEMSTOREコマンド、あるいは、これまでデータベースに送信された問い合わせを元にインデックスを自動的に作成し、ユーザは何も指定する必要がないという、賢いRDBMSかもしれません。