UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Primary Key and Foreign Key    
(Redirected from Primary Key)
Primary Key and Foreign Key

Contents

Primary Key (PK)

A Primary Key (PK) is the name of the field or combination of fields that uniquely identifies each record.

It is common to define an AutoNumber field in each table to serve as the PK.


Foreign Key (FK)

A Foreign Key (FK) is the name of a field (or fields) that relates a record to another table. If the PK in the main table is an AutoNumber, then the Data Type of the FK in the related table should be Long Integer (Number).


Example Report

Assume you have the following information:

Image:rpt_Phones.jpg


Table Structure

While you might be tempted to create just one table for this information, that would not be the most flexible way to store it. Each contact may have no phone or several. Each phone number has a type such as Home, Office, Fax, or Cell.

Set up the following tables:

Field Name Data Type, Size Field Description Default Value

t_Contacts

CID AutoNumber
stored as Long Integer
CID (PK)  
NameMain Text, 75 Last Name (human) or Company name  
NameFirst Text, 50 First Name (human)  
IsHuman Yes/No Is this a human contact? True
noteCont Text, 50 note about contact  
dtmAdd Date/Time date/time record was added =Now()

t_Phones

PhoneID AutoNumber PhoneID (PK)  
CID Number, Long Integer Contact Person/Company (FK to t_Contacts)  
PhoTypeID Number, Long Integer Phone Type - ie: Primary, Home, Work, Cell, Fax, etc. (FK to t_PhoTypes)  
Ext Text, 10 Phone Extension  
notePho Text, 50 note about phone  
dtmAdd Date/Time date/time record was added =Now()

t_PhoTypes

PhoTypeID AutoNumber PhoTypeID (PK)  
PhoType Text, 50 Phone Type  
dtmAdd Date/Time date/time record was added =Now()


Tracking Field

Each table has a tracking field called dtmAdd that is automatically filled out with the date and time the record was created. This is accomplished using Now() as the default value, which is a built-in function that returns the date and time of the system clock.

Example Records

Here are examples of records your tables may contain. Each table has an AutoNumber field that is designated as the Primary Key. AutoNumbers automatically get a value when a new record is created. Access ensures this value is distinct.

Contacts

 Image:Datasheet_Contacts.jpg

Phone Types

Image:Datasheet_PhoTypes.jpg

The values of an AutoNumber don't make sense and they don't need to. The only thing that matters is that the values are unique for each record.

Phones

Image:Datasheet_Phones.jpg

The Phones tables has 2 foreign keys:

  1. CID identifies the contact that the phone number belongs to.
    For example: CID=1 —> MainName = Smith, NameFirst = Mary

  2. PhoTypeID specifies the type of phone number
    For example: PhoTypeID=104 —> PhoType = Cell


Relationship Diagram

Layout your relationship diagram so that each table with a Primary Key is to the left of each table with a related Foreign Key. This way, the diagram will flow from left to right as data must be entered.

 Image:rel_PK_FK.jpg

One Contact may have many Phone numbers. There is a one-to-many relationship between Contacts and Phones.

One Phone Type may be used in many Phone records. There is a one-to-many relationship between Phone Types and Phones.

Edit Discussion
Thank you for your support!
This page has been accessed 9,400 times.  This page was last modified 04:59, 5 February 2012 by Jack Leach. Contributions by strive4peace2011  Disclaimers