MySQL: Use of 'key (field_name)' at end of CREATE TABLE statements

[callumd]

In MySQL, I understand what a primary key is, and what it's used for. I'm just not sure why we are forced to declare them? What does putting 'PRIMARY KEY(field_name)' at the end of a CREATE TABLE statement actually do?

[callumd]

MySQL: In some MySQL tutorials, the fields are defined, and then the primary key is declared 'PRIMARY KEY(field_name)' at the end of the CREATE TABLE statement. Sometimes, a more "keys" are defined: 'key (field_name)'. What does this latter code mean/do?

[NogDog]

Merged these two threads into one.

[NogDog]

From the MySQL manual (http://dev.mysql.com/doc/refman/4.1/en/create-table.html): "A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL." This provides a column which will always have a unique value and which is indexed, making it the primary column on which the table will be indexed (which optimizes queries and sorts). KEY by itself is the same as PRIMARY KEY when used in a column definition, and is a synonym for INDEX when used at the end of the table definition (or as part of a column definition when using a version of MySQL < 4.1).

[callumd]

So what is the difference between putting "PRIMARY KEY(field_name)" at the end of a CREATE TABLE statement..

..compared with..

Declaring field_name as NOT NULL when you initialise it, and then applying a UNIQUE index to it (UNIQUE(field_name)) at the end of the statement?

[chazzy]

primary keys are indexed as such and are typically how the primary means of sorting is handled for the table. unique keys are sorted, but do not guarantee the O(1) that primary keys can typically achieve.

Are you familiar with HashMaps/HashTables etc...?