What's different between "PRIMARY KEY" and "KEY"?

I want to know what’s different between “PRIMARY KEY” and “KEY” in MYSQL RDBMS?

A Primary Key is a unique identifier for a record in a table that is used to represent the whole record. A Unique key is just a field that cannot exist more than once in a table. So no two records can have the same primary key, and no two records can have the same unique key. For example, you can have a customerid as a primary key (e.g. a23deacbe231 - a random unique string, so another user can’t have the same string as their customerid) and a username as a unique key (e.g. JSmith - so another user can’t choose the username JSmith).

A Foreign key is the primary key of a different table used for relational purposes. So if you want to link the table customers and purchases, you would have the primary key customerid be a FOREIGN key in the purchases table, because each purchase needs to have a customer.

Purchases Table

purchaseid     itemname     customerid
1              stove        a23deacbe231                  

(john smith bought a stove)

Thank you svcghost
I got it.
But i need to know more about “key”. Just “key” index.
As we know we have the following indexing types:

  • Primary Key: Identity Key for a record.
  • Unique Key: a unique value cannot exists in another record.
  • KEY ???

What if i use “key” without any type of indexing?

Taken from a SITE POINT BOOK: SIMPLY SQL BY Rudy Limeback chapter 10

A key is simply the terminology we use in databases to mean an identifier:
a means to identify, unambiguously and uniquely, a particular instance of an entity. When we store or retrieve data in a database table that contains entities, each instance must be uniquely distinguished from every other instance of the same type of entity. This can only be done with a key that has unique values for all instances.

For example, we’ve seen SQL queries with identifiers such as customer_id and forum_id. These identifiers are valid keys because every instance, every value, represents a different entity. All the values are unique. It’s unlikely we’d ever think of assigning the same customer_id value to two different customers, nor the same forum_id value to two different forums. Is this too obvious? It seems like only common sense, and, yes, it really is that simple.

Primary KEY:
So these examples of identifiers are unique keys. Then what is a primary key? A primary key is simply any one of the keys that an entity may have. The reason we need to pick one of these keys, and designate it as the primary key, is so that foreign keys or related entities will have a designated key that they can relate to.

Take you, for example: what is it about you that identifies you? your name is not a good key, because others may share the same name. Some possible keys that would be unique might be a representation of your fingerprints, or your retinal pattern, or even your DNA sequence. Let’s leave aside for the moment some obvious questions of practicality—such as whether these identifiers could be forged, whether they’re accurate enough, or even what to do about identical twins—and concentrate only on their uniqueness. Assuming for the sake of argument that we accept these identifiers as being capable of uniquely identifying every person in our application, we now have three unique keys to choose from. We pick one of them—even if we plan to store all three—and call it the primary key.

Hope this helps!:smiley:

A key is simply the terminology we use in databases to mean an identifier:
That mean the “key” is just a terminology? Right?
If so, I can create an index by:

create index myindex
using btree
on author

I want to know what type of indexing above?

i will admit i don’t know much about indexing but maybe the links below should clear up things a bit:

SQL Index

SQL Indexes

hey azdrian, thanks for the reference :smiley:

@ web.designer.iq –

in mysql, a PRIMARY KEY is what you would expect, and is the same concept as in other database systems

however, in mysql, a KEY is simply an INDEX – it doesn’t have to be unique

does that help?