Home and Learn: Microsoft Access Course
Access Primary Keys and Foreign Keys
In this lesson, you'll learn about Primary and Foreign keys. They are important concepts in database tables. Let's see what they are.
Primary Keys
How do you tell the difference between these two records in a database table?:
John Smith, 10 High Street
John Smith Jr, 10 High Street
Both men are called John Smith, and both live at 10 High street. The only difference is that one is called John Smith Junior and the other just John Smith. Access would have to examine the data in every cell (field) in the table, playing spot-the-difference. If it's a big table, this could take some time.
Instead, you can add a unique identifier as a field. The simplest way to do this is with numbers:
1, John Smith, 10 High Street
2, John Smith Jr, 10 High Street
Now Access just has to look at the unique numbers. As long as you're not allowed to use the same number, then you have an identifier for each row. In database terminology what we've done is created something called a Primary Key.
A Primary Key is used to uniquely identify a row (record) in your table. When you create a new table, Access automatically adds a field called ID and sets this as your Primary Key. It sets this to the data type AutoNumber. When you add a new record, Access then increments your Primary Key, making sure that there are no duplicates. (A field used as a Primary Key can't have duplicate values. It's also a required field, meaning you can't leave it blank.)
You don't have to use incrementing numbers as the values for a Primary Key. You can use anything you like. As long as you don't have the same value more than once, Access doesn't care. (It's not a good idea to use sensitive personal information as a Primary Key, though. You wouldn't set a Social Security number as a Primary Key, for example.)
Foreign Keys
So, how can you link two table together? With Primary Keys. What you do is create a field in the second table that will hold the Primary Key values from the first table. Take this as an example. Suppose you had a list of widgets you sell:
1, Blue Widget
2, Green Widget
3, Yellow Widget
4, Red Widget
Your first list is then this:
The Primary Key in this list is WidgetID. It has unique values, and none are repeated. Every cell has a value. There are no missing ones. (The same is true for the Widget column, but numbers are easier for a Primary Key.)
Now, suppose you had a second list. This:
Here are the two lists side-by-side:
You can see that Widget number 1 was ordered 2 times, Widget 2 was ordered 3 times, Widget 3 was ordered 2 times and that Widget 4 was ordered 2 times. Replace the numbers in the second column of the orders list with the Widget names and it's easier to see:
What we've done here is to add the Primary Key from the Widgets list to the orders list. But once WidgetsID is in the orders table, it's no longer a Primary Key. It's said to be a Foreign Key. A Foreign Key column can have duplicates. Indeed, that's the whole point.
What we have here is something called a one-to-many relationship, with the Primary Key and Foreign Key acting as a link. We have one value in the Widgets ID list that has many values in the orders list. (You can also have a one-to-one relationship and a many-to-many relationship. As one-to-many relationships are the most common, we'll concentrate on those.
OK, that's the theory. But how does it work in practice? Let's see. It's quite easy. In the next lesson, we'll link our two tables together.
<-- Previous Lesson: Access Dropdown Lists | Next Lesson: Linking Database Tables in Access -->
<--Back to the Microsoft ACCESS Contents Page