One of the first concepts that new users of Access have to wrap their head around is that of keys. A key is a way of connecting one record in a database with another record. Just like you have a different key for your car, your house and your office, each record will have a different key. Having the key gives you access to the record. Simple, right?
Even though some people resist the idea — “I am not just a number” — the fact is that we all use keys every day of our life. Whether it is a social security number, bank account number or employee number, we’ve all been assigned unique numbers that we have to memorize and use to access our information.
To illustrate how Primary Keys and Foreign Keys work, I’m going to use the simple example below of a company database that has a list of customers in one table, and a list of invoices in a separate table.
Primary Keys
Each record in a database is (usually) going to need a Primary Key. The Primary Key for each record has to be unique inside that table of the database, and is oftentimes a number. For example, a list of customers might have a field called customerNumber. Each record has a unique customerNumber, so we can safely use it as a Primary Key.
Remember, the most important aspect of a Primary Key is that it has to be unique inside the table. That also means that Primary Keys never get re-used. If you have a customer with the customerNumber of 123456, you’ll never use that number again, even if the person never shops with your business again and/or you delete them from the table entirely.
How to designate a field as the Primary Key
Once you understand keys, it’s relatively easy to tell Access to use a certain field as the Primary Key.
- Make sure you have your table open in Design View.
- Click your mouse in the field you want to use as a Primary Key.
- Click the Primary Key button in the Tools group on the Table Tools Design tab.
Foreign Keys
Another advantage of using keys in a database is that it allows us to reduce redundancy of information. In our example, the table of invoices has a Primary Key field called invoiceNumber, but it needs some way to link to the table of customers so that you can mail invoices to the correct customer. Rather than repeat the customers’ names and address in the invoice table, we’ll just include a column called customerNumber_FK that has the Primary Key from the customers table in it.
When a Primary Key appears in another table, we call it a Foreign Key. Foreign Keys do not have to be unique. One Best Practice I like to follow when naming Foreign Key fields is to use the exact same field name as it appears in the parent table, but append _FK to the end of the name. That way when I am creating relationships it is easy to see the connection: customerNumber connects to customerNumber_FK. That is a Best Practice for humans, but not required by the computer.
