Table Relationships in MS Access

A table relationship establishes a connection between records in different tables. Usually, a primary key in one table is connected to a matching foreign key in another table.

Example: Students and Courses

Students Table

Courses Table

In this example, CourseID connects the Students table to the Courses table. Rahul and Amit are enrolled in the course identified by CourseID 1.

• Reduce unnecessary duplication of data.
• Connect information stored in different tables.
• Improve data consistency.
• Help enforce referential integrity.
• Allow related information to be retrieved through queries.
• Support forms and reports that display related records.

Without Relationships

If the course name is stored repeatedly for every student, changing a course name may require updating many records.

With Relationships

Course information can be stored in one Courses table. Student records can refer to the appropriate course using CourseID.

Primary Key

A primary key uniquely identifies each record in a table. Its values cannot be duplicated or left null.

Example: StudentID in the Students table.

Students
--------------------------
StudentID  (Primary Key)
StudentName
CourseID

Foreign Key

A foreign key is a field that refers to a key in another table. It is used to establish a relationship between the tables.

Courses
--------------------------
CourseID    (Primary Key)
CourseName

Students
--------------------------
StudentID   (Primary Key)
StudentName
CourseID    (Foreign Key)

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 employee table connected to a separate employee-details table where each employee has one matching details record.

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. Each record in the second table refers to at most one record in the first table through that key.

Example: One course can have many students.

Many-to-Many Relationship

In a many-to-many relationship, multiple records in one table can relate to multiple records in another table. This is normally implemented using a junction table.

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

One-to-many relationships are common in database applications. A parent record can be associated with multiple child records.

Example: Department and Employees

The DepartmentID in Employees refers to the DepartmentID in Departments. Several employees may share the same DepartmentID.

Microsoft Access provides a Relationships window where you can connect tables visually.

Steps to Create a Relationship

1. Open your Access database.
2. Make sure the tables you want to connect already exist.
3. Open the Database Tools tab.
4. Click Relationships.
5. Use Show Table or Add Tables to add the required tables.
6. Locate the matching primary key and foreign key fields.
7. Drag the primary key field onto the matching field in the other table.
8. Review the Edit Relationships dialog box.
9. Select Enforce Referential Integrity if appropriate.
10. Click Create.
11. Save the relationship layout.

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

Example

If CourseID 1 and CourseID 2 exist in the Courses table, a student can refer to either course. A student should not refer to CourseID 99 when that course does not exist, if referential integrity is enforced and the relationship requires a matching course.

Benefits

• Prevents invalid foreign-key references.
• Helps maintain consistent related data.
• Protects the database from certain accidental changes.

Steps to Enable It

1. Open the Relationships window.
2. Double-click the relationship line.
3. Check Enforce Referential Integrity.
4. Review other options if required.
5. Click OK.
6. Save the relationship layout.

Cascade Update Related Fields

When supported and enabled, cascade update propagates a change to a referenced key to matching foreign-key values in related records.

Cascade Delete Related Records

When enabled, deleting a parent record can also delete related child records. This should be used carefully because it can remove many records.

A many-to-many relationship is commonly implemented by creating a third table called a junction table or linking table.

Example: Students and Courses

Students Table

StudentID (Primary Key)
StudentName

Courses Table

CourseID (Primary Key)
CourseName

Enrollments Table

StudentID (Foreign Key)
CourseID  (Foreign Key)
EnrollmentDate

Each enrollment record connects one student to one course. Multiple enrollment records allow a student to take multiple courses and a course to have multiple students.

Relationships

• Students to Enrollments: one-to-many.
• Courses to Enrollments: one-to-many.

Together, these two relationships implement the many-to-many association between Students and Courses.

The Relationships window allows you to review the connections between tables.

Common Tasks

• Add tables to the Relationships window.
• Create a relationship by connecting matching fields.
• Open relationship properties.
• Enable or review referential integrity.
• Remove an incorrect relationship when necessary.
• Arrange tables so that the diagram is easy to read.

Steps to Edit a Relationship

1. Open Database Tools and select Relationships.
2. Double-click the relationship line.
3. Review the related fields.
4. Change supported options if required.
5. Click OK and save the layout.

Removing a relationship line removes the defined relationship, not the table records themselves. Always verify the effect before making changes.

Problem 1: Relationship Cannot Be Created

Solution: Check the field data types, field sizes, and key definitions.

Problem 2: Referential Integrity Cannot Be Enabled

Solution: Check for unmatched foreign-key values, null values where not allowed, and incompatible field definitions.

Problem 3: Duplicate Values in a Primary Key

Solution: Ensure the primary key uniquely identifies every record. A field with duplicate values cannot serve as a standard primary key.

Problem 4: Related Data Is Not Displayed as Expected

Solution: Verify that the foreign-key values match the intended primary-key values and that your query or form uses the correct relationship.

Problem 5: Records Are Deleted Unexpectedly

Solution: Review Cascade Delete settings and restore data from a backup if needed.

Create related Students and Courses tables in Microsoft Access.

Students Table

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

Courses Table

• CourseID — AutoNumber, Primary Key
• CourseName — Short Text

Tasks

1. Create the Courses table.
2. Create the Students table.
3. Add some course records.
4. Add students with valid CourseID values.
5. Open Database Tools and select Relationships.
6. Add both tables.
7. Connect Courses.CourseID to Students.CourseID.
8. Enable Enforce Referential Integrity.
9. Save the relationship layout.
10. Create a query that displays student names and course names.

1. What is a table relationship in MS Access?
2. What is a primary key?
3. What is a foreign key?
4. What is a one-to-one relationship?
5. What is a one-to-many relationship?
6. How is a many-to-many relationship implemented?
7. What is referential integrity?
8. What does Cascade Update do?
9. Why should Cascade Delete be used carefully?
10. Where do you create relationships in MS Access?