Solved

Mysql Indexes and Primary vs Unique

Posted on 2008-11-02
4
908 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

896 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now