1. What Is a Foreign Key?

In a relational database, a foreign key is a field (or combination of fields) in one table that points to the primary key of another table.

Primary key = unique identifier in the main table (e.g., CustomerID in the Customers table).
Foreign key = field in another table that refers to that primary key (e.g., CustomerID in the Orders table).

This creates a relationship between the two tables. It tells Access:

“Every order must belong to a valid customer.”

So, if a CustomerID does not exist in the Customers table, it cannot be used in the Orders table when referential integrity is enabled.

Simple Example

Table: Customers
CustomerID (Primary Key)
FirstName
LastName
Table: Orders
OrderID (Primary Key)
OrderDate
CustomerID (Foreign Key → points to Customers.CustomerID)

Here, Orders.CustomerID is the foreign key.

2. Why Foreign Keys Are Important in Access

Creating foreign keys in Access is not just a “good practice”; it protects your data and makes your reports more reliable.

2.1 Data Consistency

Without foreign keys, you could accidentally enter an order with CustomerID = 999 even if no such customer exists. With a foreign key and referential integrity, Access prevents this.

2.2 Easier Queries and Reports

Once tables are related properly, you can easily build queries that join tables, such as:

Show all orders with customer name
Show total sales per customer
Show customers who haven’t placed an order yet

2.3 Avoid Duplicate Data

Instead of repeating customer details in the Orders table, you store customer info once in Customers and refer to that row via the foreign key. This reduces redundancy.

3. Preparing Tables for Foreign Keys

Before you create a foreign key relationship in Access, make sure your tables are ready.

3.1 Ensure Primary Keys Are Set

The table you are linking to (the “parent” or “lookup” table) must have a primary key.

Open the table in Design View.
Select the field you want as the primary key (e.g., CustomerID).
Click the Primary Key icon on the ribbon (a key symbol).

You’ll see a little key icon next to the field name.

3.2 Match Data Types

The foreign key field in the “child” table must have the same data type as the primary key in the parent table.

Examples:

If Customers.CustomerID is AutoNumber, then Orders.CustomerID should be Number (Long Integer).
If primary key is Short Text, foreign key must also be Short Text with compatible field size.

If data types don’t match, Access won’t allow you to create the relationship.

4. How to Create a Foreign Key in Access (Using Relationships Window)

The most common way to create a foreign key in Access is via the Relationships window. Here’s the step-by-step process.

Step 1: Open the Relationships Window

Open your Access database.
Go to the Database Tools tab on the ribbon.
Click Relationships.

If this is your first time, the window might be empty.

Step 2: Add Tables to the Relationships Window

In the Relationships window, click Show Table (or right-click in the blank area and select Show Table).
Select the tables you want to relate (e.g., Customers and Orders).
Click Add for each table, then Close.

You’ll now see both tables as boxes with their fields listed.

Step 3: Drag Primary Key to Foreign Key

Click and hold the primary key field in the parent table (e.g., CustomerID in Customers).
Drag it to the matching field (foreign key) in the child table (e.g., CustomerID in Orders).
When you release the mouse, the Edit Relationships dialog box appears.

Step 4: Configure Relationship Settings

In the Edit Relationships dialog:

Confirm the correct fields are selected under Table/Query and Related Table/Query.
Check Enforce Referential Integrity if you want to prevent orphan records.
Optionally, check:
Cascade Update Related Fields – if the primary key changes, foreign key updates automatically.
Cascade Delete Related Records – if a parent record is deleted, all child records are deleted too. Use with caution.

Click Create.

Step 5: Save the Relationship Layout

When you close the Relationships window, Access will prompt you to save. Click Yes to save the relationships layout.

You have now created a foreign key relationship between the two tables.

5. Creating a Foreign Key While Designing a New Table

Sometimes you create the child table after the parent table. You can build the foreign key field right in Design View.

Step 1: Design the Child Table

Go to Create → Table Design.
Add your fields (e.g., OrderID, OrderDate, CustomerID).
Set OrderID as the primary key.

Step 2: Add the Foreign Key Field

For CustomerID, choose a Number data type (Long Integer) if the parent’s CustomerID is AutoNumber.
Optionally, set a Lookup for user-friendly display:
In Field Properties → Lookup tab, set Display Control = Combo Box and look up values from the Customers table.

Step 3: Save Table and Create Relationship

Save the table (e.g., as Orders).
Then follow the same Relationships window steps from the previous section to link it to Customers.

6. Enforcing Referential Integrity in Access

When you check Enforce Referential Integrity, Access enforces three main rules:

You can’t enter a foreign key value in the child table that doesn’t exist in the parent table.
You can’t delete a record from the parent table if related child records exist (unless you use cascade delete).
You can’t change the primary key in the parent table to a value that would break existing foreign keys (unless you use cascade update).

Benefits

Prevents “orphan” records (e.g., orders pointing to deleted customers).
Keeps your data clean and trustworthy.
Reduces logical errors in your reports and queries.

Cascade Options Explained

Cascade Update Related Fields
If a parent key is updated, all related foreign keys are automatically updated.
Example: If CustomerID changes from 5 to 15, all Orders with customer 5 become 15.
Cascade Delete Related Records
If you delete a parent record, all related records in the child table are deleted.
Example: Deleting a customer deletes all their orders.
Use it carefully—once deleted, data is not easily recoverable.

7. One-to-Many and Many-to-Many Relationships

7.1 One-to-Many (Most Common)

When a single record in the parent table relates to many records in the child table.

One Customer → Many Orders
One Category → Many Products

Foreign key relationships in Access usually represent one-to-many connections.

7.2 Many-to-Many via Junction Table

In Access, a true many-to-many relationship (e.g., one student can enrol in many courses, and one course can have many students) is done using a third table called a junction table.

Example:

Table Students (StudentID PK)
Table Courses (CourseID PK)
Table StudentCourses (junction) with:
StudentID (FK → Students)
CourseID (FK → Courses)

You create two foreign keys in the junction table:

StudentCourses.StudentID → Students.StudentID
StudentCourses.CourseID → Courses.CourseID

Both are created the same way via the Relationships window.

Error 1: “Relationship Must Have the Same Number of Fields”

This usually happens when you drag mismatching fields. Make sure you drag CustomerID to CustomerID, not to some other field.

Error 2: Data Type Mismatch

If the primary key is AutoNumber and the foreign key is Short Text, Access won’t allow the relationship. Change the foreign key to Number (Long Integer).

Error 3: Existing Data Violates Referential Integrity

If you try to enforce referential integrity but there are already orphan records (e.g., orders with customer IDs that don’t exist), Access will give an error.

Fix:

Clean the data first:
Delete or fix orphan rows in the child table.
Or add missing rows in the parent table.
Then try enabling referential integrity again.

Error 4: Locked Tables

If tables are open in Datasheet View, Access may not let you create relationships. Close them, then open the Relationships window.

9. Best Practices for Foreign Keys in Access

Always define primary keys first before foreign keys.
Use meaningful field names like CustomerID, OrderID, ProductID, etc.
Enforce referential integrity unless you have a very specific reason not to.
Avoid putting business logic (e.g., prices, names) in multiple tables; store them once and reference with keys.
Document your relationships—especially for large projects—so other people understand your design.

(FAQs)

Q1. Do I always need a foreign key in Access?
You don’t have to, but if your tables are related, foreign keys are strongly recommended. They keep data connected and prevent inconsistent entries.

Q2. Can I create a foreign key without the Relationships window?
Yes. You can define relationships using SQL (e.g., ALTER TABLE with FOREIGN KEY constraint) in Access queries, but the Relationships window is simpler for beginners.

Q3. What happens if I don’t enforce referential integrity?
Access will still allow you to create a relationship, but it won’t stop invalid data. You may end up with orders linked to non-existent customers or products.

Q4. Can a foreign key be part of the primary key?
Yes, especially in junction tables. For example, StudentCourses might have a composite primary key made of StudentID + CourseID, both of which are foreign keys.

Q5. How do I delete a foreign key relationship in Access?
Open the Relationships window, right-click the relationship line between two tables, and choose Delete. This removes the relationship, not the actual data.

Final Thoughts

Knowing how to create a foreign key in Microsoft Access is essential for anyone serious about database design. Foreign keys:

Connect your tables
Protect your data
Make queries and reports far more powerful

By understanding primary keys, matching data types, the Relationships window, and referential integrity, you can build professional-quality Access databases that scale as your project grows.

How to Create a Foreign Key in Microsoft Access (Step-by-Step Guide for Beginners)