MS Office User

Creating Primary Keys and Foreign Keys in Microsoft Access

by on Feb.02, 2011, under Access

Working with the data in Microsoft Access tables is very important and is often difficult for new users to understand how to properly design tables to store data in the most efficient manner possible. This article has been submitted to discuss the use and creation of Primary Key and Foreign Key fields in Microsoft Access tables to help properly break down data into its core components and distribute those components efficiently between multiple tables.

Primary Keys and Foreign Keys in Microsoft Access

The use of Primary Keys and Foreign Keys in the table design of a Relational Database is very important. First, let’s discuss each of these concepts and what these terms really mean:

  • The term Primary Key (PK) in a Relational Database System refers to a field (or combination of fields) whose values uniquely identify each record in that table. In Access, it is common to define a Primary Key field in each table, usually as a field that is of AutoNumber data type, to serve as the PK. This AutoNumber field will automatically create a unique number for each record as it is created, so that those records can be identified from the other records in the table.
  • The term Foreign Key (FK) in a Relational Database System refers to a field that in a table that stores values from a Primary Key field in another table, to relate the two records to each other.

For example, assume that you have some contacts for which you want to store some information, and that information happens to include the phone numbers for each contact. You realize that some contacts have more phone numbers than others and you want to store the data in your database application as efficiently as possible. So, you decide to create an Access database application that has a Contacts table and a PhoneNumbers table to store this data. In your database application, you need a way to identify any number of phone number records that relate to each contact record. If the PK in the Contacts table is an AutoNumber type field, then the FK in the PhoneNumbers table should store the ContactID (AutoNumber) from the Contacts table and will need to be the type of Long Integer (Number). This means that a record in the PhoneNumbers table has the potential to contain the ContactID value of the contact record in the related Contacts table.

Note:
While you might be tempted to just create a couple of fields in the Contacts table to store the phone number information, that would not be the best (or Normalized) method for storing the phone data. Also consider that using the two table design, any contact record can have any number of phone number records associated with it. Whereas, if you kept everything in the Contacts table, you would have to create a separate field for each phone number that you want to store. However, each contact record may have no phone, one phone, or even several phone numbers associated with it, so many of the records in the Contacts table would have empty (wasted) space for the record. Using the two table method and relating the records between the tables, we can properly store any number of phone number records for each contact record, without wasting any space in either table.

Step-by-Step

So, to show how to create a working example of the database application example above, assume you have the following information:

Figure 1: Contact and Phone Number Data

Figure 1: Contact and Phone Number Data

Create three tables in your Microsoft Access database with the following design. Note that these tables could contain other fields too, but for this example, only contain the fields required to illustrate the points about using PKs and FKs for related data between tables:

tblContacts table
Field Name Data Type, Size Field Description Default Value
ContactID AutoNumber The Contact ID (PK)
NameFirst Text, 50 The Contact’s First Name
NameLast Text, 50 The Contact’s Last Name
DateAdded Date/Time The date/time the record was added =Now()

tblPhoneTypes table
Field Name Data Type, Size Field Description Default Value
PhoneTypeID AutoNumber The Phone Type ID (PK)
PhoneType Text, 20 The Phone Type name. The records of this table should contain the at least the following values: Primary, Home, Work, Cell, Fax, Other.
DateAdded Date/Time The date/time the record was added =Now()

tblPhoneNumbers table
Field Name Data Type, Size Field Description Default Value
PhoneID AutoNumber The Phone ID (PK)
ContactID Number, Long Integer The Contact Person/Company (FK lookup to tblContacts)
PhoneTypeID Number, Long Integer The Phone Type – ie: Primary, Home, Work, Cell, Fax, etc. (FK lookup to tblPhoneTypes)
PhoneNumber Text, 20 The Phone Number
PhoneExt Text, 10 The Phone Extension
DateAdded Date/Time The date/time the record was added =Now()

Note:
Notice that each table has a date tracking field called DateAdded that is automatically assigned the current date and time (by the use of the Now() function in the Default Value property) when the record is created. It is generally recommended for good housekeeping to know when a record in a table has been created, and usually, the user of the application never directly interacts with the data stored in this field.

Now that you have created the tables, here are examples of actual records that your tables could contain. Note that each table has an AutoNumber field that is designated as the PK. When entering data for the record, an AutoNumber field automatically gets populated with the next value when a new record is created. Access ensures this value for each record is unique.

Figure 2 - Sample Data for the Contacts Table

Figure 2 – Sample Data for the Contacts Table

Figure 3 - Sample Data for the PhoneTypes Table

Figure 3 – Sample Data for the PhoneTypes Table

Figure 4 - Sample Data for the PhoneNumbers Table

Figure 4 – Sample Data for the PhoneNumbers Table

In this case, the PhoneNumbers table has two FKs: ContactID and PhoneTypeID:

  1. The ContactID field in the PhoneNumbers table specifies which contact the phone number record relates to. For example, the value of 1 for the ContactID field specifies the record corresponding to “Crystal Long” in the Contacts table.
  2. The PhoneTypeID field in the PhoneNumbers table specifies which phone type the phone number record is designated. For example, the value of 3 for the PhoneTypeID field specifies the record corresponding to the “Work” record in the PhoneTypes table.

So, in this case, the Relationships diagram for these tables looks like the image shown in Figure 5 below. The PhoneNumbers table has one PK and two FKs, each relating to a PK for both the Contacts and PhoneTypes tables.

Figure 5 - The Relationships Diagram

Figure 5 – The Relationships Diagram

So, as a result of this design, in this database, the tables have the following relationships:

  1. One record in the Contacts table may have many related records in the PhoneNumbers table. This means that there is a one-to-many relationship between Contacts and PhoneNumbers tables.
  2. One record in the PhoneTypes table may be used in many records in the PhoneNumbers table. This means that there is a one-to-many relationship between PhoneTypes and PhoneNumbers tables.

Submitted By: Crystal – February 1, 2011


Random Posts:

  1. Creating a new blank database using Microsoft Access 2007
  2. ASCII Character Table for ISO 8859-1
  3. Using the Graphics Manager to work with Images in Publisher 2007
  4. Creating Primary Keys and Foreign Keys in Microsoft Access
  5. Using ShapeSheets in Microsoft Visio 2007
  6. Showing Hidden and System Objects in the Navigation Pane
  7. The Access 2010 Programmer’s Reference gets a 5-star review!
  8. Saving a Report as PDF with the Access 2010 GUI
  9. Can’t Delete File in Windows Vista or Windows 7 – Problem Solved!
  10. Connecting Microsoft Outlook 2007 to GoDaddy email with SSL

:, , , , , , , , , , , , ,

Leave a Reply

You must be logged in to post a comment.



Archives