MySQL: index vs. primary vs. unique?

ppc_michael

Guest
Original poster
Apr 26, 2005
1,498
2
Los Angeles, CA
What is the difference between "index," "primary," and "unique," etc, that you can set a field to? I just want to have an auto-incrementing "id" field so that I can have a unique ID number for each entry. I usually use "unique" for that, but is that semantically correct?
 

tutubibi

macrumors 6502a
Sep 18, 2003
538
0
localhost
Not really a formal definition, but here it is:

Primary Key - Key to the specific record, tells you what uniquely identifies that record
Index - one of more fields indexed to improve access time
Unique Index - type of index, means that a field or group of field is unique, so no other record can have the same one

Note that "primary" is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity ...)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).