Which index to use - Primary key, Index, etc

[bettina@coaster.ch]

I have the following tables (simplified example):

countries: code_country (char-4) , name_contry)
breweries: code_brewery (int-4), name_brewery)
coasters (code_coaster (char-8), description, code_country,
code_brewery)

code_coaster is formed by the combination from code_country + a
correlative number: for example CH-33, INDO-102, etc

How should I define the indexes so that the querries will be optimized?
I've thought about the following:

code_country: unique in table country
code_brewery: unique in table brewery

code_country: index in table coasters
code_brewery: index in table coasters
code_coaster: unique in table coasters

I don't really understand the difference between primary key and
unique. Should I use primary key instead of unique?

Thanks in advance

[NC]

Re: Which index to use - Primary key, Index, etc

bettina@coaster .ch wrote:[color=blue]
>
> I don't really understand the difference between primary key and
> unique.[/color]

The difference is very simple. There can be many unique indexes
in one table, but no more than one primary key. When MySQL is
undecided about what index to use for a particular query, it
chooses the primary key.

Cheers,
NC

[Gordon Burditt]

Re: Which index to use - Primary key, Index, etc

>I don't really understand the difference between primary key and[color=blue]
>unique. Should I use primary key instead of unique?[/color]

A primary key is, essentially, the first unique index. The distinction
is mostly for the programmer and automatic tools that diagram
relationships between tables.

How do you tell which of a pair of twins is the twin, and which one
is a real child?

Gordon L. Burditt

[jerry gitomer]

Re: Which index to use - Primary key, Index, etc

Gordon Burditt wrote:[color=blue][color=green]
>>I don't really understand the difference between primary key and
>>unique. Should I use primary key instead of unique?[/color]
>
>
> A primary key is, essentially, the first unique index. The distinction
> is mostly for the programmer and automatic tools that diagram
> relationships between tables.[/color]
Gordon L. Burditt

Be aware that this is RDBMS dependent as in some vendors
databases NULL is not a permitted value in the primary key
index, but is permitted in the unique key index.

HTH
Jerry