"Peter H. Coffin" <hellsop@ninehells.com> wrote in message
news:slrndlnr24.8mk.hellsop@othin.ninehells.com...
> On Sun, 23 Oct 2005 14:38:54 +0200, Christian Kirsch wrote:
>> Tony Marston wrote:
>>
>>> An index is a non-unique key.
>>
>> That makes me wonder why CREATE INDEX has the optional UNIQUE
>> keyword...
>
> Tony's perception differs from mine. To me, a key is a column to which
> other tables relate to this one, and that is the only quality that makes
> "keyness".

A key, whether it be a primary key or a candidate key, does not have to be
referenced from other tables in order to be known as a "key". It does not
*have* to be, but it usually is. In that other table it is known as a
"foreign key" as it points to a key in a different (foreign) table. THAT is
the difference between a primary key and a foreign key.

It is also possible for a foreign key to be a primary key at the same time.
It is a "primary" key because it is a unique key for the table in which it
is defined, but it is also a "foreign" key because it points to the primary
key of another table.

--
Tony Marston

http://www.tonymarston.net



> An index is a method of optimizing access by a particular
> column into the table in question, but there may not be any particular
> relationship to other tables for an indexed column. For example, a date
> column may likely be indexed to provide sorted and ranged lists, but is
> rarely used as a join column in any table relationship. However, since
> the key function is almost invariably benefits from speedy access by the
> column, an index is built on the column as a matter of course. Checking
> for uniqueness is easy to do in the routines that maintain the index,
> and while that checking is *very* useful in a "key column" context, it's
> not only applicable in that context, so rolling that functionality into
> the index functions makes a great deal of sense.
>
> --
> Crowds want to beat, journalists deserve to be beaten. Where lies
> the problem?
> -- Lars Syrstad