Solved

Mysql Indexes and Primary vs Unique

Posted on 2008-11-02
4
910 Views
Last Modified: 2013-12-13
I have been working on simple database stuff.

Took the suggestions from here...

But wondering a little bit more about why.

*Indexes and Primary vs Unique*

From my reading, I have set up my table to have "userName" be Unique...

Because I don't want two values to be the same.

Not really sure why I have my "memberID" set up for Primary...and not Unique...

Now, both are indexs.

Why would I want that? Or is that just a default of what happens when I make something a Primary / Unique.

I am just guessing that it makes the data look different than the other columns when doing querys.

Thanks for the help
0
Comment
Question by:cntmedia
4 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 22862398
memberID is an integer column, uniquely identifies record and can be referenced by a foreign key. Hence, it is declared as PK & an index is created in the background. By the way foreign keys are allowed in non-default innoDB tables. Normally ID/CODE columns are made as entity-relationship keys.
userName has to be just unique but maybe even null, hence unique constraint &/ unique index is created on that.
It depends whether default is clustering index or not for a PK in the type of your table. If it is, then that will be  the only one allowed for that table. Good also, since it is PK which has got high cardinality i.e. set of unique values and does not get normally updated & nowadays people resort to soft deletion to preserve all data in table itself.
Clustered index is physically sorted on disk, hence tends to get fragmented requiring defragmentation via reorg based on how much activity occured against this index. Non-clustered indexes which are allowed more than one on a table contain data as in table column with pointers retrieving the sorted order.
You are correct, data will be different, one will be ID's i.e. integer and the other Names i.e. characters.
0
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 125 total points
ID: 22862411
It sounds like you're asking two different questions.

First, why would a PRIMARY KEY be listed as an INDEX?  Well, that's exactly what it is!  A table's primary key is its most important index.

Every table must have exactly one primary key (i.e., it has to have one specified, and it can't have two).  Note that you can have several different FIELDS in your primary key, but there's only ever one primary key.  This key must uniquely identify every row of the table, and it shouldn't ever change (e.g., once you've assigned someone memberID #192, they should stay member #192 forever).

Generally, the most efficient query you can run over a table's data is to select a single row using its primary key.  What's the record for member #192 look like?  Easily found!

Second, what's the difference between a PRIMARY KEY and a UNIQUE index?  Essentially, you've already picked a primary key (memberID), but you've realized you also have another field in your table that you want to use to uniquely identify rows.  Since you've already named memberID as your primary key, you can designate username as an ordinary UNIQUE field.

Both should indeed get indexed, since you'd like queries for both a memberID or for a username to be particularly efficient.

Does that answer your question?
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 22865427
Primary = Primary key index
Unique = No duplicates allowed
0
 

Author Closing Comment

by:cntmedia
ID: 31512512
Thanks for the answer
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question