Jump to content
Larry Ullman's Book Forums

Mysql Syntax


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.

Link to comment
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
Link to comment
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)

Link to comment
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!

Link to comment
Share on other sites

 Share

×
×
  • Create New...