2. Primary Key

Primary keys are an essential part of relational database design.

The 'primary key' of a table uniquely identifies each record..

Consider the 'City' database table we used before.

Notice that each record now has a unique identity defined in the field CityID.

Many primary keys are single field values but more complex situations may use several attributes to define a primary key. Then it is called a 'compound primary key'.

Notice the (AutoNumber) feature in the Access database screen-shot above. Because primary keys have to be unique a common feature of database systems is to have an 'auto-increment' field. Whenever a new record is added, the field value is incremented.

This is convenient but it is by no means essential. There are other ways to create primary keys.

Additional point

Primary keys do not have to be numbers, they can be anything that makes each record unique.

For example an username-password table might have a fixed length 'hash' value as its primary key, like this:

A hash value is simply a fixed length character string that is created by doing some calculations on one or more field values.

For example joe and ninety could be combined into a single string 'joeninety' that is then encrypted into a unique fixed length hash. This approach allows the same username to be used more than once as long as the passwords are different and vice versa.