views:

109

answers:

4

When should I use KEY, PRIMARY KEY, UNIQUE KEY and INDEX?

+3  A: 

KEY and INDEX are synonyms.

You should add an index when performance measurements and EXPLAIN shows you that the query is inefficient because of a missing index. Adding an index can improve the performance of queries (but it can slow down modifications to the table).

You should use UNIQUE when you want to contrain the values in that column (or columns) to be unique, so that attempts to insert duplicate values result in an error.

A PRIMARY KEY is both a unique constraint and it also implies that the column is NOT NULL. It is used to give an identity to each row. This can be useful for joining with another table via a foreign key constraint. While it is not required for a table to have a PRIMARY KEY it is usually a good idea.

Mark Byers
@Mark Byers PRIMARY KEY is used to give an identity to each row? explain sorry for my ignorance.
needHELP
@needHELP: It is used as a way to uniquely identify any row in your table. For example if you want to delete a specific row in your table it's useful to have some way to unambiguously identify it so that you don't accidentally delete the wrong row. It's also useful for joining with another table as it is indexed.
Mark Byers
-1 for the really poor title of the question
Ivo van der Wijk
@Ivo: That comment ended up in the wrong place :)
Daniel Vassallo
oops, fixed :) The -1 *was* on the original question though
Ivo van der Wijk
+2  A: 

KEY and INDEX are synonyms in MySQL. They mean the same thing. In databases you would use indexes to improve the speed of data retrieval. An index is typically created on columns used in JOIN, WHERE, and ORDER BY clauses.

Imagine you have a table called users and you want to search for all the users which have the last name 'Smith'. Without an index, the database would have to go through all the records of the table: this is slow, because the more records you have in your database, the more work it has to do to find the result. On the other hand, an index will help the database skip quickly to the relevant pages where the 'Smith' records are held. This is very similar to how we, humans, go through a phone book directory to find someone by the last name: We don't start searching through the directory from cover to cover, as long we inserted the information in some order that we can use to skip quickly to the 'S' pages.

Primary keys and unique keys are similar. A primary key is a column, or a combination of columns, that can uniquely identify a row. It is a special case of unique key. A table can have at most one primary key, but more than one unique key. When you specify a unique key on a column, no two distinct rows in a table can have the same value.

Also note that columns defined as primary keys or unique keys are automatically indexed in MySQL.

Daniel Vassallo
can I have both a unique key and primary key with the same value?
needHELP
@needHELP: It depends. You mean on different columns?... Can you give an example?
Daniel Vassallo
the same column and table
needHELP
@needHELP: If you have a table called `passengers` with the following fields: `(id, first_name, last_name, age, passport_number)`, you'd normally set up the `id` column to be the primary key. A primary key is automatically a unique key. The main difference is that you can only have one primary key on the table, and that the column cannot contain `NULL` values. There is no need to set up the `id` column to be `UNIQUE` because by setting it to a primary key, it's automatically guaranteed to be unique... Then you could also set `passport_number` to `UNIQUE` so that no more than one passenger...
Daniel Vassallo
... would be able to have the same passport number. However you might have some passengers (children for example) without a passport number. In that case, you can insert `NULL` in that column, without problems.
Daniel Vassallo
A: 

PRIMARY KEY AND UNIQUE KEY are similar except it has different functions. Primary key makes the table row unique (i.e, there cannot be 2 row with the exact same key). You can only have 1 primary key in a database table.

Unique key makes the table column in a table row unique (i.e., no 2 table row may have the same exact value). You can have more than 1 unique key table column (unlike primary key which means only 1 table column in the table is unique).

INDEX also creates uniqueness. MySQL (example) will create a indexing table for the column that is indexed. This way, it's easier to to retrieved the table row value when the query is queried on that indexed table column. The disadvantage is that if you do many updating/deleting/create, MySQL has to manage the indexing tables (and that can be a performance bottleneck).

Hope this helps.

The Elite Gentleman
A: 

Unique Keys: The columns in which no two rows are similar

Primary Key: Collection of minimum number of columns which can uniquely identify every row in a table (i.e. no two rows are similar in all the columns constituting primary key). There can be more than one primary key in a table. If there exists a unique-key then it is primary key (not "the" primary key) in the table. If there does not exist a unique key then more than one column values will be required to identify a row like (first_name, last_name, father_name, mother_name) can in some tables constitute primary key.

Index: used to optimize the queries. If you are going to search or sort the results on basis of some column many times (eg. mostly people are going to search the students by name and not by their roll no.) then it can be optimized if the column values are all "indexed" for example with a binary tree algorithm.

Ashwini Dhekane