Understanding Primary And Foreign Keys In Microsoft Access

Access is designed as a simple way to build databases for people who are not necessarily technically minded. You do not have to learn an extensive and complicated coding language to create or manage the database. It has an advantage over excel as statistics can be outputted along with reports at the touch of a button via a professional front-end you can create through the use of simple wizards. It is far easier to manage and protect the data in Access than in Excel and can provide a professional front end to your data allowing complete data entry control. There are a few concepts it is important to understand before designing and building the database. One such concept is the use of Primary and Foreign Keys.

Data is stored in access databases in tables. A table is a list of rows and columns containing data. There are usually lots of tables per database. Imagine you needed to build a simple database of customers. The first table you would build is the Customer table, which we will call 'Tbl_Customer.' You must break down the data to its simplest form and not attempt to put all the information into one large table. Consider only the simplest information about the customer. In this table each Customer will have its own record, one record only per customer. Do not be tempted to put all the customer's sales etc in this table as the table will be far too large and impossible to manage. You will also have more than one record per customer and as this table is going to be used to define each customer in the database, that is unacceptable. In Tbl_Customer will be the first name, surname, first line address, second line address, Town, County, Postcode and an ID number that will be an automated number unique to the customer. This number is called a Primary Key. The most important thing to remember is that this number is always unique to each record. No two records will ever have the same number as a Primary Key.

One advantage of having a Primary Key per record is that this number can be placed in other tables as a 'Foreign Key.' If you were to create a table containing all the sales records, there would be a lot of sales information. It would be impractical to have to repeatedly add customer names and addresses in this second table. It would be a waste of space, disorganised and unnecessarily repetitive. It would also lead to duplicates since spelling mistakes on the name, for example, may lead to the same customer being entered several different times. When a report is created to show the sales per customer, one customer may have several different totals due to such errors. Database cleansing to eradicate such problems is a difficult and expensive process so it is preferable to use the Primary Key and Foreign Key method to link tables as a preventative measure.

The Foreign Key is essentially the Primary Key from one table placed in another table in order to join them. When creating a report to see how many sales there are per customer, access will look at a sales record, see that there is a Foreign Key and look in the relating customer table at the matching number to obtain the customer details. Data only needs to be entered once. When creating a front end for the users to enter the sales, a field can be placed on a form, which will automatically show the customer names, and the user can simply choose from the list. Behind the scenes, access will have placed the Primary Key for the selected customer in the Foreign Key field on the sales table.

The Primary Key is therefore vital to define individual records. In reality this is vital when there are likely to be extremely large databases containing thousand if not millions of records. The Foreign Key method allows cross-referencing of tables and records, which is particularly important in modern databases, which can contain thousands of separate tables. The cross-referencing and joining of the tables create relationships between the tables, which is what makes a relational database possible and practical.