SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question What's different between "PRIMARY KEY" and "KEY"?

    Hi
    I want to know what's different between "PRIMARY KEY" and "KEY" in MYSQL RDBMS?

  2. #2
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by web.designer.iq View Post
    Hi
    I want to know what's different between "PRIMARY KEY" and "KEY" in MYSQL RDBMS?
    A Primary Key is a unique identifier for a record in a table that is used to represent the whole record. A Unique key is just a field that cannot exist more than once in a table. So no two records can have the same primary key, and no two records can have the same unique key. For example, you can have a customerid as a primary key (e.g. a23deacbe231 - a random unique string, so another user can't have the same string as their customerid) and a username as a unique key (e.g. JSmith - so another user can't choose the username JSmith).

    A Foreign key is the primary key of a different table used for relational purposes. So if you want to link the table customers and purchases, you would have the primary key customerid be a FOREIGN key in the purchases table, because each purchase needs to have a customer.

    E.g.
    Purchases Table
    Code:
    purchaseid     itemname     customerid
    1              stove        a23deacbe231
    (john smith bought a stove)

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you svcghost
    I got it.
    But i need to know more about "key". Just "key" index.
    As we know we have the following indexing types:

    • Primary Key: Identity Key for a record.
    • Unique Key: a unique value cannot exists in another record.
    • KEY ???

    What if i use "key" without any type of indexing?

  4. #4
    SitePoint Member azdrian18's Avatar
    Join Date
    Aug 2010
    Location
    Trinidad and Tobago
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Taken from a SITE POINT BOOK: SIMPLY SQL BY Rudy Limeback chapter 10

    A KEY:
    A key is simply the terminology we use in databases to mean an identifier:
    a means to identify, unambiguously and uniquely, a particular instance of an entity. When we store or retrieve data in a database table that contains entities, each instance must be uniquely distinguished from every other instance of the same type of entity. This can only be done with a key that has unique values for all instances.

    A KEY EXAMPLE:
    For example, we’ve seen SQL queries with identifiers such as customer_id and forum_id. These identifiers are valid keys because every instance, every value, represents a different entity. All the values are unique. It’s unlikely we’d ever think of assigning the same customer_id value to two different customers, nor the same forum_id value to two different forums. Is this too obvious? It seems like only common sense, and, yes, it really is that simple.

    Primary KEY:
    So these examples of identifiers are unique keys. Then what is a primary key? A primary key is simply any one of the keys that an entity may have. The reason we need to pick one of these keys, and designate it as the primary key, is so that foreign keys or related entities will have a designated key that they can relate to.

    PRIMARY KEY EXAMPLE:
    Take you, for example: what is it about you that identifies you? your name is not a good key, because others may share the same name. Some possible keys that would be unique might be a representation of your fingerprints, or your retinal pattern, or even your DNA sequence. Let’s leave aside for the moment some obvious questions of practicality—such as whether these identifiers could be forged, whether they’re accurate enough, or even what to do about identical twins—and concentrate only on their uniqueness. Assuming for the sake of argument that we accept these identifiers as being capable of uniquely identifying every person in our application, we now have three unique keys to choose from. We pick one of them—even if we plan to store all three—and call it the primary key.


    Hope this helps!

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A KEY:
    A key is simply the terminology we use in databases to mean an identifier:
    That mean the "key" is just a terminology? Right?
    If so, I can create an index by:
    Code MySQL:
    create index myindex
    using btree
    on author
    (name)
    I want to know what type of indexing above?

  6. #6
    SitePoint Member azdrian18's Avatar
    Join Date
    Aug 2010
    Location
    Trinidad and Tobago
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    i will admit i don't know much about indexing but maybe the links below should clear up things a bit:

    SQL Index

    SQL Indexes

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    hey azdrian, thanks for the reference

    @ web.designer.iq --

    in mysql, a PRIMARY KEY is what you would expect, and is the same concept as in other database systems

    however, in mysql, a KEY is simply an INDEX -- it doesn't have to be unique

    does that help?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •