Contents

Tables and the primary key

As you have learned in the previous pages, data in a database is stored in tables that contain rows or records. Earlier in this database design tutorial you saw an example of a table containing information about tutorials. Lets look at that table again (below).

table-tutorials

A table with tutorials

The tutorial table consists of 6 tutorials. All 6 tutorials are different, but for each tutorial the same fields, namely tutorial_id, title and category, are stored in the table. The tutorial_id is the primary key of the tutorial table. The primary key is a value that is unique for each record in a table.

In the customer table below, the customer_id is te primary key. It is also a unique number for each record.

Primary keys in everyday life

Primary keys are used to identify records in a database. They are all around us. Every time you encounter a unique number it might serve as a primary key in a database (but it doesn't necessarily have to be used as such. All databases are capable of automatically generating an incremental numeric key for each record).

  • The order number you receive when you shop online could be the primary key of some order table in the shop's database, because it is a unique value.
  • Your social security might be a primary key in some government database.
  • An invoice number could be used as a primary key in a database table that stores invoices that have been sent to clients.
  • A numeric customer ID is often used as a primary key in a customers table.
  • A product number often refers to a product number primary key field in a 'products' table.
  • etc.

Characteristics of the primary key

Below are the characteristics of the primary key.

Primary keys identify data

Primary keys are used to identify records in a table. When you call a service desk and the service desk agent asks for your customer id he/she is really asking "by which unique code can our database identifiy you in the customers table?"

If you forgot your customer id, the service desk agent will have to look for different pieces of information that the database can use to uniquely identify you, like the combination of your birthdate and last name.

A primary key is unique

A primary key is always a unique value, because if it were not unique, it couldn't be used to uniquely identify records in a table. This means that a value can occur only once in a primary key column. Relational database systems are generally programmed to disallow the insertion of a duplicate primary key value. Trying to do so will result in a database error.

Primary key data types

The primary key of a table is often a number, but it can be of any datatype. It is not uncommon to use a string (a string is a piece of text) as a primary key.

Composite keys

Often the primary key is made up of one field, but a primary key can also be a combination of two fields that uniquely identify a record. In this case, the combination of the two values must be unique in the table. More on this later in this article.

Autonumbering

Primary key fields are often, but not always, managed by the database. You can tell a database to automatically assign a unique numeric primary key to each new record in the table. Databases will often start numbering at 0 and increment the primary key value for each new record. Such a primary key is called an auto-incrementing or autonumbering key. Auto-incrementing keys are a good way to ensure that each records gets a unique primary key. Another name for such a primary key field is a surrogate key. Since the key is not an actual part of the data that is stored in the table (like a user), it is called "surrogate".