SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,145
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post What is the difference of this two

    Hi, can you help me please to enlighten my mind what is the difference of this two "UNIQUE" and "INDEX" in MySQL,can you please give some little example so that i can understand thoroughly.and also when to use this "UNIQUE" and "INDEX" in my tables.


    Thank you in advance

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,279
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    I'm sure Rudy will shoot me down if my answer is incomplete, but essentially...

    index is simply a means to look up specific rows on a table. Using an index saves the database from having to search each and every row individually. Think of it like an index in the back of a text book. You can use the index at the back of the book to look for a specific topic and find it easily without having to page each and every page to find it.

    unique is used when you want to have a value exist exactly once in a column. It allows you to identify individual rows on a table.

    If you're of a type that documentation makes sense to you, you can refer to the mySQL page specifically....http://dev.mysql.com/doc/refman/5.0/...ate-index.html
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,880
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    Hi Jem,

    Community->Member List

    Take a look at the above link and do an advanced search for Jem.

    A popup box should appear showing numerous members names beginning with Jem. You will notice that there is an alphabetical INDEX on the members names but only one UNIQUE (oxymoron occurrence of a member's name.
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    I'm sure Rudy will shoot me down if my answer is incomplete
    na, man, nice job

    confusion might arise from the fact that a unique constraint is actually implemented as a unique index -- so if you declare a column as UNIQUE, you do ~not~ need an additional index on it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,279
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    confusion might arise from the fact that a unique constraint is actually implemented as a unique index -- so if you declare a column as UNIQUE, you do ~not~ need an additional index on it
    True......if something is defined is unique, it's an index by default. But an index does not have to be unique.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,145
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    na, man, nice job

    confusion might arise from the fact that a unique constraint is actually implemented as a unique index -- so if you declare a column as UNIQUE, you do ~not~ need an additional index on it
    HI ,I have question but if we have primary key,..I know primary key is unique, so i don't need anymore to add "unique index" on my table?...sorry i am confuse on this...please correct me if i am wrong...

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,279
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by jemz View Post
    HI ,I have question but if we have primary key,..I know primary key is unique, so i don't need anymore to add "unique index" on my table?...sorry i am confuse on this...please correct me if i am wrong...
    Correct, unless there is a non-identifying field that you would for some off reason want one and only one value of.

    An example would be a table of users. The primary key would identify each individual record. Now, if for some odd reason, your customer decides they only want one person from each town, you would put a unique index on the town field. From a semantic perspective, you don't want to make this the primary key because the table is dealing with users, so you declare an extra unique index.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    I know primary key is unique, so i don't need anymore to add "unique index" on my table?
    definitely not on the same column(s)

    but you can also declare a unique index on a different column

    for example, a table of users which has an auto_increment primary key, but you also want to ensure that no two users have the same name, so you declare a unique index on the name

    yes, you could use the name as the primary key, but because users are referenced in many many tables (e.g. last_updated_by columns), you would prefer to use the 4-byte integer as the foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,145
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    HI @DaveMaxwell and @r937, Thank you for helping me...

  10. #10
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    For completeness sake I'd like to add that it possible to add an index or a unique index to multiple columns at once. So if you want to allow multiple users with the same username but only if they have different email addresses for example you can add a unique index to (username, email). That way the combination of the username and email have to be unique, not the columns themselves.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •