4

Possible Duplicate:
mySQL's KEY keyword?

Like

PRIMARY KEY (ID),
KEY name (name),
KEY desc (desc),

etc.

what are they useful for?

Community
  • 1
  • 1
Elle
  • 49
  • 1
  • 1
  • 2
  • See various other questions: http://stackoverflow.com/questions/2199285/primary-key-versus-key, http://stackoverflow.com/questions/924265/mysqls-key-keyword, etc – Lukas Eder Aug 26 '11 at 14:07

6 Answers6

6

Keys are used to enforce referential integrity in your database.

A primary key is, as its name suggests, the primary identification of a given row in your table. That is, each row's primary key will uniquely identify that row.

A unique key is a key that enforces uniqueness on that set of columns. It is similar to a primary key in that it will also uniquely identify a row in a table. However, there is the added benefit of allowing NULL in some of those combinations. There can only be 1 primary key, but you can have many unique keys.

A foreign key is used to enforce a relationship between 2 tables (think parent/child table). That way, a child table can not have a value of X in its parent column unless X actually appears in the parent table. This prevents orphaned records from appearing.

Derek
  • 21,828
  • 7
  • 53
  • 61
  • Excellent explanation. Just add the normal index to the answer, and it would be the most complete answer of the question ;) – Maxim Krizhanovsky Aug 26 '11 at 14:13
  • Only the foreign key enforces referential integrity; a primary key would if the table is self-referential (hierarchical data). – OMG Ponies Aug 26 '11 at 14:15
  • 1
    KEY is used to create an index, and it doesn't have to enforce referential integrity. A FOREIGN KEY enforces referential integrity. The way the table in example was structured - no referential integrity was enforced, they are simply indexes. You didn't explain what's the actual real world use of the index - performance. I'm not downvoting, but you should really explain it all :) – N.B. Aug 26 '11 at 14:15
  • I'll leave OMG Ponies' to explain it since he's done a good job and I'd feel dirty essentially copy/pasting what he's already said. I'm more familiar with SQL Server, so I wasn't sure what that syntax would mean and decided to give a general view of how "keys" work. – Derek Aug 26 '11 at 14:27
4

The primary key constraint ensures that the column(s) are:

  • not null
  • unique (unique sets if more than one column)

KEY is MySQL's terminology in CREATE TABLE statements for an index. Indexes are not ANSI currently, but all databases use indexes to speed up data retrieval (at the cost of insertion/update/deletion, because of maintenance to keep the index relevant).

There are other key constraints:

  • unique
  • foreign key (for referential integrity)

...but your question doesn't include examples of them.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
2

keys are also called indexes. They are used for speeding up queries. Additionally keys can be constrains (unique key and foreign key). The primary key is also unique key and it identifies the records. The record can have other unique keys as well, that do not allow to duplicate a value in a given column. Foreign key enforces referential integrity (@Derek Kromm already wrote excellent description). The ordinary key is used only for speeding up queries. You need to index the columns used in the WHERE clause of the queries. If you have no index on the column, MySQL will need to read the whole table to find the records you need. When index is used, MySQL reads only the index (which is usually a B+ tree) and then read only those record from the table it found in the index.

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
1
  1. Primary KEY is for creating unique/not null constraint for each row in the table. Also searching by this key is the fastest. You can create only one PK in the table.
  2. Ordinary key/index is key for speeding your searching by this column, sorting, grouping and joining with other table by this key.

Indexes drawback:

Adding new indexes to table will influence on speed or running insert/update/delete statements. So you should select columns for indexing in your table very carefully.

Andrej
  • 7,474
  • 1
  • 19
  • 21
  • MySQL (and SQL Server) use the clustered key for the primary key indexing. I'm not positive that you can create a clustered key on a column that is not the primary key in MySQL, though you can in SQL Server... – OMG Ponies Aug 26 '11 at 14:13
  • @OMG-Ponies Some notes. In mysql clustered index is in only InnoDb table. Primary key is clustered by default. If you create table without primary key, clustered index will be created by default but will be invisible. – Andrej Aug 26 '11 at 14:18
  • InnoDB is the only real engine to me ;) "By default" isn't saying that a clustered key *has* to be the primary key, that it could be column(s) other than the primary key (or part of). – OMG Ponies Aug 26 '11 at 14:20
  • So then you can set all fields KEYs to speed up searches ?:) – Elle Aug 26 '11 at 14:21
  • No, in this case your insert/update statements will run slowly. – Andrej Aug 26 '11 at 14:23
  • @OMG-Ponies No, As I wrote only primary or unique/not_null indexes http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html – Andrej Aug 26 '11 at 14:24
0

Key are used for relation purposes between tables and you are able to create joins in order to select data from multiple tables

PDB
  • 103
  • 2
  • 3
  • 13
  • They are also a unique identifier of that particular table, e.g UserID – PDB Aug 26 '11 at 14:07
  • Although keys are important when joining tables (because of performance) keys can be used in simple queries like select * from users where username = ''; – Maxim Krizhanovsky Aug 26 '11 at 14:09
  • This applies only to primary keys or unique key (implying that the value of the column won't be `NULL`) – cypher Aug 26 '11 at 14:10
0

What, you didn't fine the wikipedia entry comprehensive? ;-)

So, a key, in a relational database (such as MySQL, PostgreSQL, Oracle, etc) is a data constraint on a column or set of columns. The most common keys are the Primary key and foreign keys and unique keys.

A foreign key specifically relates the data of one table to data in another table. You might see that a table blog_posts has a foreign key to users based on a user_id column. This means that every user_id in blog_posts will have a corresponding entry in the users column (this is a one-to-many relationship -- a topic for another time).

If a column (or group of columns) has a unique key, that means that there can only be one such incidence of the key in the table. Often you'll see things like email addresses be unique keys -- you only want one email address per user. I've also seen a combination of columns match to a unique key -- the five columns, first_name, last_name, address, city, and state, will often be a unique key -- realistically, there can only be one William Gates at 1835 73rd Ave NE, Medina, Washington. (I do realize that it is possible for a William Gates Jr. to be born, but the designers of that database didn't really care).

The primary key is the primary, unique identifier of a given table. By definition it is a unique key. It is something which cannot be null and must be unique. It holds a special place of prominence among the indexes of a given table.

Community
  • 1
  • 1
cwallenpoole
  • 79,954
  • 26
  • 128
  • 166