Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hi,

 

I am studying the book "effortless e commerce." There are a couple of lines I don't understand.

 

UNIQUE KEY category (category)

KEY user_id (user_id)

 

Why are they writing the same name twice? and What's the "KEY"? I only know PRIMARY KEY() and UNIQUE() from the book "PHP6 and MYSQL 5"

 

For example, PRIMARY KEY(id) means id is a primary key to that column. And if you put UNIQUE (username) means username can not be duplicated.

 

I don't understand these UNIQUE KEY category (category) and KEY user_id (user_id)...

 

Thanks for your help in advance and look forward to hearing from you soon.

Share this post


Link to post
Share on other sites

 

UNIQUE KEY category (category)

KEY user_id (user_id)

 

Why are they writing the same name twice?

 

The first occurrence is just the index name. You could write UNIQUE KEY some_other_name (category) if it's clearer for you. I've often seen index names such as category_unq or user_id_pk or some other other suffix that makes it immediately clear what kind of index it is.

  • Upvote 1

Share this post


Link to post
Share on other sites

I'm not familiar with "KEY" on its own, and I haven't used the E-commerce book so far. So I'll let someone else answer!

Share this post


Link to post
Share on other sites

This is an interesting question (and one I have always wondered about). After a fair amount of research (and learning on my own part), I can conclude the following:

 

KEY and INDEX are the same meaning (as are UNIQUE KEY and UNIQUE INDEX). With that said, INDEX seems to be the more "proper" term, although they're the same, and I hear "primary key" all the time, but never "primary index". (Maybe it's a MySQL thing.)

 

Anyway, an INDEX/KEY creates a separate copy of the table with the column or columns that are marked with INDEX/KEY. The column is then reorganized (at least, with a typical non-clustered index) to make it easier/faster to search through for information.

 

To summarize, INDEX/KEY make a database faster to search through, but slower to insert data into and larger in size.

 

And as you stated in your first post, Ryan R, the UNIQUE version means that all the values in a column/field must be unique, including NULL (i.e., there can only be one (or no) NULL value). If a non-unique value is placed in a UNIQUE column, then an error occurs.

 

Primary keys are like UNIQUE INDEX/KEY with some additional rules, such as negative/NULL values not being allowed (and also, primary keys generally never change).

 

Any type of UNIQUE INDEX offers additional constraints to the data, which basically restrict the type of data allowed. If a constraint is broken, an error occurs.

 

The best explanation I saw for all this was actually on Wikipedia (surprise! surprise!). See the following for more info:

 

http://en.wikipedia.org/wiki/Index_(database)

Share this post


Link to post
Share on other sites

I normally use the term "INDEX" in my SQL but phpMyAdmin outputs SQL commands using "KEY", which is why you'll sometimes see "KEY" in the book.

Share this post


Link to post
Share on other sites
I hear "primary key" all the time, but never "primary index". (Maybe it's a MySQL thing.)

Interesting point, however, "Primary Key" is the standard naming convention used in all RDBMSs for the column which uniquely identifies each record in the table.

 

Also, thanks for the link!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...