Mysql Indexes and Primary vs Unique

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
cntmediaAsked:
Who is Participating?
 
VoteyDiscipleCommented:
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
 
k_murli_krishnaCommented:
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
 
NerdsOfTechTechnology ScientistCommented:
Primary = Primary key index
Unique = No duplicates allowed
0
 
cntmediaAuthor Commented:
Thanks for the answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.