When primary / index / unique !?

[Anon]

Hi,

something which always confuses me, is when to make a column "primary", "index" or "unique" im MySQL.

Can somebody please explain to me, when each of these classification (or combination thereof) is appropriate?

Schouldn't a "primary" key always be "unique"? Or does "primary" automatically imply "unique"?

And when should I use "index"?

Slightly confused in Germany,

Roman

[brandonschnell]

use primary to build an auto_index (which are unique by definition). only one primary column so pick which ever column is most important.

use unique when data should not repeat in a specified column. (ie social security numbers)

use index when you need to speed up data access in a specific column (commonly used columns like username)

[Anon]

Primary key implies unique AND not null

Unique allows NULL values, but all others are unique.

An index allows fast access to values in a column, under certain conditions.

Primary keys and unique constraints are usually implemented as an index! (Because it's the fastest way to check if a new value to be inserted already exists in the column)

Some databases (probably mysql) hide the fact that an index is created to make a PK or UNIQUE work and some don't - eg. PostgreSQL.

Arguably every table should have a primary key. Go here: http://techdocs.postgresql.org and read the article 'Essential Databasing Terminology'.

Chris