Creating Table Relationships: Understanding Primary and Foreign Keys in Microsoft Access

Image by Editor
 

Microsoft Access is widely used for developing desktop-based relational database systems. Database relationships are essential for organizing data efficiently in Microsoft Access. At the core of any well-structured Access database lies the concept of table relationships, primarily built through primary keys and foreign keys. These elements ensure data integrity, enable efficient queries, and eliminate redundancy.

In this tutorial, we will show how to create table relationships while understanding the primary and foreign keys in Access.

What Are Primary and Foreign Keys?

Primary Keys

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. Each table should have one and only one primary key. Consider it a unique identifier, like a Social Security number or student ID.

Characteristics of a Primary Key:

• Must contain unique values.
• Cannot contain null values.
• Usually implemented as an AutoNumber field in Access.
• It can consist of one or more fields (known as a composite key), though single-field keys are most common.
• It forms the foundation for table relationships.
• Allow Access to find and retrieve specific records quickly.

In a Customers table:

• CustomerID can be the primary key because each customer has a unique ID.

Foreign Keys

A foreign key is a field in one table that refers to the primary key in another table. It creates a link between the two tables, establishing a relationship.

Characteristics of a Foreign Key:

• Can contain duplicate values.
• Can accept null values (depending on design).
• Enforces a relationship to another table’s primary key.
• Ensures referential integrity when properly set.
• Connect related data across tables.
• Allow for efficient querying of related information.

In an Orders table:

• CustomerID is a foreign key that refers to the CustomerID in the Customers table.

Types of Table Relationships

• One-to-One: Each record in Table A relates to exactly one record in Table B.
• One-to-Many: Each record in Table A relates to multiple records in Table B.
• Many-to-Many: Multiple records in Table A relate to multiple records in Table B (requires a junction table).

Create Tables and Set Primary Keys in Access

Let’s create a simple but practical two-table database that tracks customers and their orders, which is a common business scenario.

Step 1: Create a New Database

• Open Microsoft Access.
• Click New and create a blank database.
• Name it CustomerOrdersDB >> click Create.

Step 2: Create the Customers Table

• Go to the Create tab >> select Table Design.
• Add the fields as specified in the table above:
  • CustomerID (Data Type: AutoNumber)
  • FirstName (Data Type: Short Text)
  • LastName (Data Type: Short Text)
  • Email (Data Type: Short Text)
  • Phone (Data Type: Short Text)
  • Address (Data Type: Short Text)

• Select the CustomerID field:
  • Go to the Table Design >> select Primary Key.
  • Or right-click >> select Primary Key.

• Right-click >> select Save >> name table as Customers.

Customers Table:

Step 3: Create the Orders Table

• Go to the Create tab >> select Table Design.
• Add the fields as specified in the table above:
  • OrderID (Data Type: AutoNumber)
  • CustomerID (Data Type: Number, Long Integer)
  • OrderDate (Data Type: Date/Time)
  • OrderTotal (Data Type: Currency)
• Select the OrderID field:
  • Go to the Table Design >> select Primary Key.
  • Or right-click >> select Primary Key.
• Right-click >> select Save >> name table as Orders.

At this point, we have two tables:

• Customers with CustomerID as the primary key.
• Orders with OrderID as the primary key and CustomerID as the foreign key.

Step 3: Establish the Relationship

3.1: Open the Relationships Window

• Go to the Database Tools tab >> select Relationships.

• Go to the Relationships Design >> select Add Tables.
• From the Add Table side bar:
  • Select Customers table >> select Add Selected Table.
  • Next, select Orders table >> select Add Selected Table.

3.2: Create the Relationship (One-to-Many)

• Drag the CustomerID field from the Customers table to the CustomerID field in the Orders table.

• The Edit Relationships dialog box will appear. In the dialog box:
• Check the Enforce Referential Integrity box to ensure that every order must correspond to an existing customer.
• Optional: Select the following options based on your data needs:
  • Cascade Update Related Fields: If a primary key value changes, the related foreign key values will update automatically. (If you want changes to CustomerID to update in Orders)
  • Cascade Delete Related Records: If a customer is deleted, all related orders will also be deleted. (If you want to delete a Customer to delete their Orders)

• Click Create to finalize the relationship.

You will now see a one-to-many relationship line between the Customers and Orders tables, indicating that one customer can have multiple orders.

Access shows a line connecting the two tables, with a 1 on the Customers side and an infinity symbol on the Orders side, indicating a one-to-many relationship.

Step 4: Add Sample Data

Add Customer Records:

• Open the Customers table by double-clicking it in the navigation pane.
• Enter the following sample data:

Add Order Records:

• Open the Orders table.
• Enter the following sample data:

Step 5: Test the Relationship

Once the relationship is in place,

• Expand the plus icon (+) in the customer table to see the order details.

• This will show the order details for each customer.

Test Referential Integrity:

• Try adding a new order in the Orders table using a valid CustomerID. Access will accept the entry.

• Try using a CustomerID that doesn’t exist in the Customers table. Access will prevent the entry and show a referential integrity violation message.
  • Try adding an order with CustomerID = 10 (which doesn’t exist in the Customers table).
  • Access displays an error message: You cannot add or change a record because a related record is required in table ‘customers’.
  • Click OK.

• Try deleting a customer with existing orders (if cascade delete is not enabled). Access will block the deletion to maintain integrity.
  • Access displays an error message: The record cannot be deleted or changed because table ‘Orders’ includes related records.
  • Click OK.

This process ensures that your database always has valid and consistent data.

Create a Query to Join the Tables:

• Go to the Create tab >> select Query Design.

• From Add Tables >> add both the Customers and Orders tables to the query.
• Notice the relationship line appears automatically.

• Add these fields to the query grid:
  • Customers.FirstName
  • Customers.LastName
  • Orders.OrderDate
  • Orders.OrderTotal
• Go to the Query Design tab >> select Run to run the query.

• You will see a result set that combines customer information with their orders.

Create a Customer Orders Form:

• Go to the Create tab >> select Form Wizard.
• Select fields from both tables:
  • From Customers: FirstName, LastName, Email, Phone.
  • From Orders: OrderDate, OrderTotal.
• Click Next.

• Select by Customers for how to view the data.
• Click Next.

• Choose a layout and style.
• Select Datasheet.
• Click Next.

• Title it Customer Orders and click Finish.

• This form will show customer information at the top and their orders in a subform below.

Best Practices for Table Relationships

• Always define a primary key for each table, preferably using the AutoNumber data type.
• Avoid changing primary key values after data entry.
• Always use referential integrity unless there’s a strong reason not to.
• Use foreign keys to link related tables and prevent data duplication.
• Avoid storing redundant or derived data that can be calculated from existing relationships.
• Normalize your database to reduce redundancy and increase flexibility.

Common Issues and Solutions

• Cannot add or change record error: This usually means you’re violating referential integrity rules.
• Data type mismatch: Ensure both the primary key and foreign key have the same data type.
• Existing data prevents relationship: If you already have data in the Orders table with CustomerID values that don’t exist in the Customers table, you’ll need to correct this before you can enforce referential integrity.

Conclusion

Understanding and correctly implementing primary keys and foreign keys in Microsoft Access is essential for building a reliable and efficient relational database. Table relationships built on these keys enable accurate data entry, maintain consistency, and support advanced queries and reporting. By following the steps in this tutorial, you can design well-structured relationships between the Customers and Orders databases that scale and adapt to growing data needs while ensuring integrity and reliability.

As you become more comfortable with relationships, you can expand your database to include additional tables like Products, Order Details, and Categories, creating a more comprehensive system.