| Lesson 3 | One-to-one and one-to-many relationships |
| Objective | Create one-to-one and one-to-many relationships. |
One-to-One Relationships in Access
In Microsoft Access, a one-to-one relationship is a type of relationship between two tables where each record in one table corresponds to exactly one record in another table, and vice versa. This relationship is established using a primary key in one table and a foreign key in the other, ensuring that the linked fields contain unique values. One-to-one relationships are less common than one-to-many or many-to-many relationships but are useful in specific scenarios, such as when you want to split a table into two for organizational purposes or to store sensitive data separately. For example, an Employee table might store general information like names and IDs, while a related Confidential table could store sensitive data like social security numbers, with each employee having exactly one matching confidential record.
To create a one-to-one relationship in MS Access, you first ensure that both tables have a primary key or a unique index on the fields that will be linked. In the Relationships window, you drag the primary key field from one table to the corresponding field in the other table, then select "Enforce Referential Integrity" in the Edit Relationships dialog box to maintain data consistency. This ensures that a record in one table cannot exist without a corresponding record in the other and prevents orphaned records. For instance, if you delete a record in the Employee table, Access can be set to automatically delete the corresponding record in the Confidential table (using the Cascade Delete option), keeping the data synchronized.
One-to-one relationships are particularly useful for improving database efficiency and security. By separating data into two tables, you can apply different security settings or optimize storage for fields that are rarely accessed. For example, a Customer table might store basic contact information, while a related CustomerPreferences table could store optional settings like marketing preferences, which only some customers provide. This structure avoids cluttering the main table with null values and allows for easier maintenance. However, one-to-one relationships should be used judiciously, as overusing them can complicate database design unnecessarily; they are best reserved for cases where data separation provides clear benefits, such as compliance with data privacy regulations or streamlined data management.
The reason I have combined creating 1) one-to-one and 2) one-to-many relationships is that Access will pretty much determine what type of relationship you will be using, based on which fields you use for the relationship and whether they are primary keys.
To create a one-to-one relationship in MS Access, you first ensure that both tables have a primary key or a unique index on the fields that will be linked. In the Relationships window, you drag the primary key field from one table to the corresponding field in the other table, then select "Enforce Referential Integrity" in the Edit Relationships dialog box to maintain data consistency. This ensures that a record in one table cannot exist without a corresponding record in the other and prevents orphaned records. For instance, if you delete a record in the Employee table, Access can be set to automatically delete the corresponding record in the Confidential table (using the Cascade Delete option), keeping the data synchronized.
One-to-one relationships are particularly useful for improving database efficiency and security. By separating data into two tables, you can apply different security settings or optimize storage for fields that are rarely accessed. For example, a Customer table might store basic contact information, while a related CustomerPreferences table could store optional settings like marketing preferences, which only some customers provide. This structure avoids cluttering the main table with null values and allows for easier maintenance. However, one-to-one relationships should be used judiciously, as overusing them can complicate database design unnecessarily; they are best reserved for cases where data separation provides clear benefits, such as compliance with data privacy regulations or streamlined data management.
The reason I have combined creating 1) one-to-one and 2) one-to-many relationships is that Access will pretty much determine what type of relationship you will be using, based on which fields you use for the relationship and whether they are primary keys.
One-to-one Relationship
Creating the one-to-one relationship found in Consulting407.mdb is done by using the ConsultingID field in both tables. Both tables use the ConsultingID field as a primary key. Remember that when a field is a primary key, each record has to have a unique value in that field. Here are the table structures for Consultants and ConsultantsBankInfo:
| Field Name | Data Type | Description | | ------------ | ---------- | ----------- | | ConsultantID | AutoNumber | | | LastName | Text | | | FirstName | Text | | | SSN | Text | |
📋 Table: ConsultantsBankInfo
| Field Name | Data Type | Description | | ------------ | --------- | ----------- | | ConsultingID | Number | | | BankName | Text | | | AcctNo | Text | | | AutoDeposit | Yes/No | |
🔍 Field Properties (for `ConsultingID` in ConsultantsBankInfo)
- Field Size: Long Integer
- Format: (Not set)
- Decimal Places: Auto
- Input Mask: (Not set)
The way to create this relationship is to:
- Choose the Relationships… option from the Tools menu to open the Relations Layout window.
- Clear any current tables displayed in the Relationship window by clicking the Clear Layout toolbar button
Clear Layout toolbar button - Add the two tables that will be used, in this case Consultants and ConsultantsBankInfo, by clicking the Add Table toolbar button,
Clear Layout toolbar button
and adding the two tables using that dialog. - After the two tables have been added, use drag and drop by clicking and holding the mouse on the field called ConsultingID in the table Consultants, then dragging it over the ConsultingID field in ConsultantsBankInfo. The Edit Relationships dialog will open, as seen here:
| Setting | Value |
|---|---|
| Table/Query | Consultants |
| Related Table/Query | ConsultantsBankInfo |
| Field from Consultants | ConsultantID |
| Field from ConsultantsBankInfo | ConsultantID |
| Enforce Referential Integrity | ✅ Checked |
| Cascade Update Related Fields | ⬜ Unchecked |
| Cascade Delete Related Records | ⬜ Unchecked |
| Relationship Type | One-To-One |
🔍 Interpretation
- The relationship is a one-to-one link between
Consultants.ConsultantIDandConsultantsBankInfo.ConsultantID. - Referential integrity is enforced, meaning:
- A record in
ConsultantsBankInfocannot exist unless a corresponding record exists inConsultants.
- A record in
- Cascade options are disabled, so:
- Updates or deletes in
Consultantswill not automatically propagate toConsultantsBankInfo.
- Updates or deletes in
Notice that the relationship type is one-to-one. Access specified this based on the fields used and the tables' primary keys.
One last thing to note is the way to get the 1 and infinity symbol to be displayed. Click the Referential Integrity check box.
Referential integrity will be discussed in greater detail in the next lesson.
One-to-Many Relationships in Access
Creating one-to-many relationships is identical to creating one-to-one relationships. The main difference is that the field used in the “many” side is not a primary key field. The field used on the many side will be what is called a foreign key. For instance, the ClientNo used in Projects is not the primary key, but a foreign key. In Clients the ClientNo is a primary key. In the next lesson, you will learn how to create a many-to-many relationship.
One To One Relationship - Exercise
Click the Exercise link below to practice creating one-to-one relationships.
One To One Relationship - Exercise
One To One Relationship - Exercise