MySQL: index vs. primary vs. unique?

Discussion in 'Web Design and Development' started by ppc_michael, Jul 27, 2007.

  1. ppc_michael Guest

    ppc_michael

    Joined:
    Apr 26, 2005
    Location:
    Los Angeles, CA
    #1
    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?
     
  2. ppc_michael thread starter Guest

    ppc_michael

    Joined:
    Apr 26, 2005
    Location:
    Los Angeles, CA
    #3
    What are the differences though, between the options? Why primary?
     
  3. tutubibi macrumors 6502a

    tutubibi

    Joined:
    Sep 18, 2003
    Location:
    localhost
    #4
    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).
     
  4. zimv20 macrumors 601

    zimv20

    Joined:
    Jul 18, 2002
    Location:
    toronto
    #5
    that's the (unique) value you can use in rows in other tables to reference rows in the table where the primary column is defined.
     

Share This Page