Databases

Primary Keys

We are now ready to save our table design, but before we do there are two issues we need to look at:-

• What name shall we give the table?
• How do we make sure that every record has a unique value?

Whats in a name?

As always, we should give the table a meaningful name, but we do not need to worry about where to save the table because Access will decide that itself. Tables are actually stored within the database itself, and we have already decided where to store the database when we created it.

We are going to have several tables, queries, forms and reports in this database, and it is a good idea to use a system that lets you tell what exactly each part of the database is.

To do this, add a prefix to the name of every table, query, form and report. This simply means that instead of calling our table "lessons" we will add the prefix "tbl" and call it "tbllessons"

Stop!

If youve already hit that "OK" button, then you Access will ask you if you want to create a primary key. As we don't know what they are at the moment, hit the "Cancel" button and we will investigate why Access considers this so important.

A computer database needs to have a unique value stored with every single record. In our timetable, lesson 1 on day 1 may have a unique key of 1, but it might just as easily have the unique key of 1243, or any other number, as long as it is unique.

Our table does not contain any field that is unique to just that lesson, and that is what Access was attempting to tell us.

We need to add an additional field to our database and set it to be a unique key field, which is called the "primary key" Access has a special data type that is ideal primary keys called an autonumber data type. An autonumber field never repeats itself, and increments (adds one) itself for every new record. If we delete the record with a primary key of 3, then Access will never use that number for the primary key again.

Setting a primary key.

Click on this image to see how to set a primary key field.

Add a primary key to your table and save the table as "tbllessons"

Click here to return to the top of the page

Click here to find out how to use your database to find information