Table Relationship Basics in MS Access

A table relationship is a connection between tables based on related fields. For example, a Students table can be connected to a Courses table using a CourseID field.

Example: Students Table

Courses Table

The CourseID field connects the Students table to the Courses table. Students with CourseID 1 are associated with the ADCA course.

• Reduce duplication: Store course details in one place instead of repeating them for every student.
• Improve consistency: Maintain a common course ID across related records.
• Retrieve related data: Display student names alongside their course names.
• Support data integrity: Relationships can help prevent invalid references.
• Simplify queries: Combine information from multiple tables in a query.

For example, if the ADCA course fee changes, you can update the fee in the Courses table instead of updating multiple student records.

Primary Key

A primary key uniquely identifies each record in a table. It cannot contain duplicate values or Null values.

Example: StudentID in the Students table.

Foreign Key

A foreign key is a field that refers to the primary key in another table. It connects related records between tables.

Example: CourseID in the Students table refers to CourseID in the Courses table.

1. One-to-One Relationship

In a one-to-one relationship, each record in one table corresponds to at most one record in the other table, and vice versa.

Example: An Employees table connected to an EmployeeDetails table containing one separate detail record per employee.

2. One-to-Many Relationship

In a one-to-many relationship, one record in the first table can be related to multiple records in the second table.

Example: One course can be associated with many students, while each student record refers to one course.

3. Many-to-Many Relationship

In a many-to-many relationship, multiple records in one table can be related to multiple records in another table. A junction table is normally used to implement it.

Example: Students can enroll in multiple courses, and each course can have multiple students.

Follow these steps to create a relationship between two tables:

1. Create the tables you want to connect.
2. Set a primary key in the parent table.
3. Add a matching foreign key field to the related table.
4. Close the tables if they are open.
5. Open the Database Tools tab.
6. Click Relationships.
7. Add the tables to the Relationships window.
8. Drag the primary key field from one table to its matching field in the other table.
9. Review the relationship options in the Edit Relationships dialog box.
10. Click Create to establish the relationship.
11. Save the relationship layout.

Referential integrity helps ensure that foreign-key values refer to existing records in the related table.

For example, if CourseID 1 exists in the Courses table, a student can refer to CourseID 1 in the Students table. If referential integrity is enforced, Access normally prevents entering a non-Null foreign-key value that has no matching parent record.

Related options

• Enforce Referential Integrity: Maintains valid relationships between records.
• Cascade Update Related Fields: Updates matching foreign-key values when a referenced key changes, where applicable.
• Cascade Delete Related Records: Deletes related child records when a parent record is deleted.

Create the following two tables.

Courses Table

• CourseID — AutoNumber (Primary Key)
• CourseName — Short Text
• CourseFees — Currency

Students Table

• StudentID — AutoNumber (Primary Key)
• StudentName — Short Text
• CourseID — Number (Long Integer)

Tasks

1. Add at least three courses to the Courses table.
2. Add five students to the Students table.
3. Assign a valid CourseID to each student.
4. Create a relationship between the two tables.
5. Enforce referential integrity.
6. Try entering a CourseID that does not exist in Courses and observe what happens.
7. Create a query displaying StudentName and CourseName together.

Goal: Understand how related tables work and how Access connects their records.

1. What is a table relationship?
2. Why are relationships important in a database?
3. What is a primary key?
4. What is a foreign key?
5. Name the three common types of relationships.
6. Where can you create relationships in MS Access?
7. What is referential integrity?
8. What does Cascade Delete do?