Chapter 7. インデックスとキー

著者: Written by Herouth Maoz

Herouth Maoz により 記述されました。

編集者より: This originally appeared on the mailing list in response to the question: "What is the difference between PRIMARY KEY and UNIQUE constraints?".

この文書は、メーリングリスト上での「PRIMARY KEY と UNIQUE 制約の違いとは」 に関する議論を元に作成しています。

題目: Re: [質問] PRIMARY KEY と UNIQUE

        PRIMARY KEY(fields,...) と UNIQUE (fields,...) との違いは何でしょうか

       - ただ名前が違うだけでしょうか?
       - PRIMARY KEY が一意性をもっているのに、どうして UNIQUE という名前の
         付いたキーがあるのでしょうか?

A primary key is the field(s) used to identify a specific row. For example, Social Security numbers identifying a person.

プライマリキーは特定の行を識別するために使われるフィールドです。例えば、人物 を識別するための社会保証番号といったものです。

A simply UNIQUE combination of fields has nothing to do with identifying the row. It's simply an integrity constraint. For example, I have collections of links. Each collection is identified by a unique number, which is the primary key. This key is used in relations.

単に UNIQUE と指定したフィールドの組み合わせは行の識別とは関係がありません。 それは単なる整合性制約です。例えば、私がリンク集をもっていたとします。 それぞれのリンク集はプライマリキーである、ユニークな番号で識別するこ とができます。このキーはリレーションのために使われます。

However, my application requires that each collection will also have a unique name. Why? So that a human being who wants to modify a collection will be able to identify it. It's much harder to know, if you have two collections named "Life Science", the the one tagged 24433 is the one you need, and the one tagged 29882 is not.

しかし、私のアプリケーションではそれぞれのリンク集が一意の名前を持 つことが必要となります。なぜでしょう? そのリンク集を編集したい人間 が識別できるようにするためです。「生命科学」と名付けられた 2 つのリン ク集がある時に、必要なものは 24433 という番号がついたリンク集であり、 29882 という番号がついたリンク集ではないことが分かることはかなり難しい ことです。

So, the user selects the collection by its name. We therefore make sure, withing the database, that names are unique. However, no other table in the database relates to the collections table by the collection Name. That would be very inefficient.

従ってユーザはその名前でリンク集を選択することになります。私たちはそ れ故にデータベースの中でその名前が一意であることを確実にします。しか し、その名前を使ってリンク集テーブルと他のテーブルとのリレーションを 持たせることはしません。効率がひどく悪いからです。

Moreover, despite being unique, the collection name does not actually define the collection! For example, if somebody decided to change the name of the collection from "Life Science" to "Biology", it will still be the same collection, only with a different name. As long as the name is unique, that's OK.

更にいうと、リンク集名は一意であるにも関わらず、リンク集自体を定義し てはいません! 例えば、誰かが「生命科学」から「生物学」へ名前を変えた としても、それは同じリンク集であり、ただ名前が変わっただけでなのです。 一意である限り、名前を変更することは問題がありません。

So:

ここまでをまとめると下のようになります。

As for why no non-unique keys are defined explicitly in standard SQL syntax? Well, you must understand that indices are implementation-dependent. SQL does not define the implementation, merely the relations between data in the database. Postgres does allow non-unique indices, but indices used to enforce SQL keys are always unique.

標準 SQL 文法上、なぜ一意でないキーが明示的に定義 されていないのかについて説明します。 まずはインデックスは実装に依存していることを理解しなくてはいけません。 SQL 文法は実装を定義してはいません。単にデータベー ス内のデータ間のリレーションを定義しているだけです。Postgres は非一意なインデックスを許していますが、SQL キーを使わなければならないインデックスは必ず一意です。

Thus, you may query a table by any combination of its columns, despite the fact that you don't have an index on these columns. The indexes are merely an implementational aid which each RDBMS offers you, in order to cause commonly used queries to be done more efficiently. Some RDBMS may give you additional measures, such as keeping a key stored in main memory. They will have a special command, for example

CREATE MEMSTORE ON <table> COLUMNS <cols>
(this is not an existing command, just an example).

このため、インデックスを付けていなくてもカラムの組み合わせによって テーブルを検索することができます。インデックスとは、良く使われる問 い合わせがより効率的に実行されるように、RDBMS が 実装した単なる補助的なものです。いくつかの RDBMS は、 主メモリ内に保持されたキーなど追加的な手法を提供しています。それら は特別なコマンドで実装されています。例えば次のようなコマンドです。

CREATE MEMSTORE ON <table> COLUMNS <cols>
(これは単なる例です。実際には存在しないコマンドです。)

In fact, when you create a primary key or a unique combination of fields, nowhere in the SQL specification does it say that an index is created, nor that the retrieval of data by the key is going to be more efficient than a sequential scan!

実際、プライマリキーまたは一意なフィールドの組み合わせを作った時に インデックスが作られるとか、そのキーによるデータ取得に際してシーケ ンシャルスキャンよりも効率的に実行される等とは、SQL 仕様のどこにも書いてありません!

So, if you want to use a combination of fields which is not unique as a secondary key, you really don't have to specify anything - just start retrieving by that combination! However, if you want to make the retrieval efficient, you'll have to resort to the means your RDBMS provider gives you - be it an index, my imaginary MEMSTORE command, or an intelligent RDBMS which creates indices without your knowledge based on the fact that you have sent it many queries based on a specific combination of keys... (It learns from experience).

ですので、一意でないフィールドの組み合わせをセカンダリキーとして使用し たい場合、何も指定する必要はありません。その組み合わせによるデータ取得 を行えば良いのです。しかし、そのデータ取得を効率的に行いたければ RDBMS 提供者が用意した方法に頼らなければなり ません。- その方法はインデックスかもしれません。私が想像した上の MEMSTORE コマンドであるかもしれません。または、あなたの知識ではなく、特定なキーの組 み合わせの問い合わせを多く送ったという事実から知的な RDBMS が (経験から学び) 作成するインデックスなのかもしれません。