Primary Key and Foreign Key
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).
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:
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.
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.
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.
The Phones tables has 2 foreign keys:
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.
One Contact may have many Phone numbers. There is a one-to-many relationship between Contacts and Phones.
|This page has been accessed 6,768 times. This page was last modified 04:59, 5 February 2012 by Jack Leach. Contributions by strive4peace2011 Disclaimers|