Exploring Primary Key's

In this blog post we are going to take a deeper look at Primary Key's and what they are used for. As we shall see, Primary Key's are fundamental to the design of a relational database. But before we go into that,  lets start at the beginning and take a look at how we create the primary key.

A primary key is something we assign to a data field, usually at the time when we create our Access Table.  The method for doing this is simple.

Creating a Primary Key

1/ Create a new database or open an existing one.

2/ Create a new new table in DESIGN VIEW.  Do this by selecting the CREATE ribbon, and then clicking the TABLE DESIGN icon in the TABLES group.

This will then open the table design grid where we can add fields to the database table.

3/ Create your first field by giving it a name (lets call it 'REF') and select the data type, AUTONUMBER, from the drop down list in the adjacent column.

4/ This is where we are going to create the PRIMARY KEY.  Make sure your cursor is located anywhere in the newly created row - if it isn't just click back into it.

5/ Click the PRIMARY KEY icon.  This is located in the TOOLS group on the DESIGN ribbon.

The Primary Key Icon

6/ This assigns the primary key to this field. You will notice the primary key symbol left of the row.

Primary Key as seen in the Table design Grid

7/ Add the any other fields that you want for this table.


Why we use Primary Key's

But lets talk a little now about what the primary key is, and what it is used for.

One purpose of the primary key is to designate one or more fields in a database table as containing a unique piece of data.  So, for example, an employer running a Personnel Database, may assign each worker their own individual employee reference number.  So in addition to storing names, addresses and dates of birth etc, each individual's personnel record will have a specific field containing an ID unique to him or her.

In the table we created above, we have used the AUTONUMBER Data Type for the field containing the primary key field.  So when we come to enter data, Access will automatically fill in this field with a unique number.  This ensures no other record in the table can have a the same ID. Moreover, when we use the AUTONUMBER Data Type, Access will not allow us to edit or change this number.  Whilst we do not need to use this Data Type for a tables primary key, it is convenient, and it is designed for this purpose.

So why, then, is it important that we have a unique piece of data to distinguish each record?

Introducing Table Relationships

How familiar are you with the term Relational Database?  I imagine you have heard the term, and may even use it on a daily basis, but what does it actually mean? Well, a relational database is where we have a single database containing a number of separate, but related, tables; and it is the primary key, which we have been exploring above, that is used to connect these tables so they relate and work together.  For example,  two tables may be connected in what is known as a one to many relationship.  The primary key field in the main table is joined to a corresponding field in the related table. This enables us to create separate tables for, say, customer details on the one hand and customer orders on the other. Connecting the two tables in a relationship using the Primary Key lets Access know which orders belong to which customers. This is only possible because the primary key field holds a unique piece of data.

An example of a one to many relationship in a customer database.

There is, of course, much more to table relationships than this.  However this a general introduction to what table relationships and primary keys are all about. In my next blog post we are going to have a go at creating a one to many table relationship, and learn more about how they work, and why they are so important in database design.