Indexes unique keys and primary keys in SQL

This article explains indexes, unique keys and primary keys. It explains what is their purpose, how to create these types of objects, and what are the differences between them.

The table below summarizes their properties:

typefaster search?rejects duplicate values?rejects null values?can be only one per table?
indexyesnonono
unique keyyesyesnono
primary keyyesyesyesyes

What is an index in SQL:

The purpose of an index is to retrieve information from a table faster.
When creating an index, you give:

  • the name of the index.
  • the name of a table.
  • the name of column(s) in this table that is (are) part of the index.

When executing a query on a table with many rows, the where condition should use the column(s) part of the index. The query will then find the result faster.

Example of index:

The table named “clients” has the columns first_name, last_name, phone_number, state,email_address,salesperson_id.
The administrator creates an index on columns first_name and last_name.

Syntax:

create index ix_clients_1
on clients(first_name, last_name)

You can make the following query faster thanks to this index:

select * from clients
where first_name = 'aamir' and last_name = 'matao'

Why do you say ‘can’ be faster, is it not always faster?

It is not always faster. In the following cases, the index is not useful:

  • if most rows have the same value in the column chosen as index.
  • if there are very few rows.

Why not putting an index on every column?:

You should use an index only when necessary, because:

  • each index takes place on the disk, just like a table does.
  • when you add an index on a table, it takes more time to update information in this table. Indeed, when you update the table, you need to update the index as well.

What is a unique key in SQL?

Unique keys also help to find information faster, but also make sure that:

  • if the unique key has one column, you cannot have two rows with the same value in this column.
  • if the unique key has several columns, you cannot have two rows with the same combination of values in these columns.

Example with one column:

create unique index ux_clients_email_address
on clients (email_address)

If you have a row in this table with email_address equal to ‘[email protected]’, you cannot insert a new row with email_address also equal to ‘[email protected]’. If you try, then you receive an error message like this one:

Duplicate entry ‘[email protected]’ for key ‘ux_clients_email_address’’

Example with two columns:

create unique index ux_clients_name_email
on clients (last_name,email_address)

What is a primary key in SQL?

A primary key can also help finding information faster and prevents duplicate values. Additionally:

  • there can only be one primary key per table.
  • some DBMSes like mySQL order the rows in the table using the column(s) related to the primary key. As a result, we will retrieve a range of value in this(these column(s) faster.
  • the primary key will prevent insertions of records for which the value of the column(s) related to the primary key is null.

Note that a primary key can also consist of more than one column.

Syntax:

alter table clients
add primary key(id)

In this statement:

  • clients is the name of the table on which we add the primary key.
  • id is the column used as primary key

What do you mean by ordering rows in the table?

When you do not specify a primary key, rows appear in the order you inserted them.

Example: you executed 3 inserts in this order (you inserted Spain first, then India, then Thailand):

insert into countries(id,name) values (2,’Spain’)
insert into countries(id,name) values (1,’India’)
insert into countries(id,name) values (3,’Thailand’)

When using the query “select * from countries”, you will see the following table:

idname
2Spain
1India
3Thailand

If you create a primary index on the column “id” (alter table countries add primary key(id)), then the same query will display this result:

idname
1India
2Spain
3Thailand

You can see that the result is ordered by id: row with id 1 (India) is first (appears at the top), row with id 2 is second, row with id 3 is third and last (appear at the bottom of the results).

What do you mean by retrieving a range of values faster?

Example: you have a primary key in column id of the table salespersons. The following query will be faster:

select * from salesperson
where id between 10 and 100

As results are ordered by id (as explained in the answer to the last question):

  • id 11 is on the line just under 10
  • 12 is on the line just under 13
  • …and so on until row 100.

As a result, once the DBMS retrieves row with id 10, it is easy for him to find the rest of the rows: they are just underneath.

If you want to test your understanding of this article, take the quiz about indexes, unique keys and primary keys. You can also learn read about foreign keys.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: November 8, 2014