Try it! Transcript



What is a one-to-one relationship?

One-to-one relationships are frequently used to indicate critical relationships so you can get the data you need to run your business.

A one-to-one relationship is a link between the information in two tables, where each record in each table only appears once. For example, there might be a one-to-one relationship between employees and the cars they drive. Each employee appears only once in the Employees table, and each car appears only once in the Company Cars table.

You might use one-to-one relationships if you have a table containing a list of items, but the specific information you want to capture about them varies by type. For example, you might have a contacts table in which some people are employees and other people are subcontractors. For the employees, you want to know their employee number, their extension, and other key information. For subcontractors, you want to know their company name, phone number, and bill rate, among other things. In this case, you'd create three separate tables—Contacts, Employees, and Subcontractors— and then create a one-to-one relationship between the Contacts and Employees tables and a one-to-one relationship between the Contacts and Subcontractors tables.

Create one-to-one relationships overview

You create one-to-one relationships by linking the index (usually the primary key) in one table and an index in another table which shares the same value. For example:

Car ID appears in both tables but is listed only once in each.

Often, the best way to create this relationship is to have the secondary table look up a value from the first table. For example, make the Car ID field in the Employees table a lookup field that looks for a value in the Car ID index from the Company Cars table. That way, you never accidentally add the ID of a car that doesn't actually exist.

Important: When you create a one-to-one relationship, decide carefully whether to enforce referential data integrity for the relationship.

Referential data integrity helps Access to keep your data clean by deleting related records. For example, if you delete an employee from the Employees table, you also delete the benefits records for that employee from the Benefits table. But in some relationships, like this example, referential integrity doesn't make sense: if we delete an employee, we don't want the vehicle deleted from the Company Cars table, because the car will still belong to the company and will be assigned to someone else.

Create a one-to-one relationship steps

Create the one-to-one relationship by adding a lookup field to a table. (To learn how, see Build tables and set data types.) For example, to indicate which car has been assigned to a specific employee, you might add Car ID to the Employees table. Then, to create the relationship between the two fields, use the Lookup Wizard:

1. Open a table.

2. In Design View, add a new field, select the Data Type value, and then select Lookup Wizard.

3. In the wizard, the default is set to look up values from another table, so select Next.

4. Select the table that contains the key (usually a primary key) that you want to include in this table, and then select Next. In our example, you'd select the Company Cars table.

5. In the Selected Fields list, add the field that contains the key you want to use. Select Next.

   

6. Set a sort order and, if you prefer, change the width of the field.

7. On the final screen, clear the Enable Data Integrity check box and then select Finish.

Want more?

Get started with table relationships

Create a relationship

Delete a relationship

Excel training

Outlook training

In a relational database, a one-to-one relationship exists when a single record in one table is related to just a single record in another table.

Although this type of relationship is not used as much as the one-to-many or many-to-many relationships, it's still good to know how it's done.

So we'll walk through the steps to create this relationship.

In this example, we have a database table for the fleet of company cars, and another table for the employees who drive them.

You can see the company uses one type of car, so the way to identify who drives which car is to use the license number field.

For this demo, let's imagine that each employee is assigned to a single car. But before we start, let's discuss a new term: "index."

Access indexes the fields in your databases, and uses them much like you use book indexes to quickly find information.

Indexes are on by default, but you can change their behavior or turn them off.

That's important because you create a one-to-one relationship by first creating a one-to-many relationship, and then changing the indexes for the key fields in both tables.

Let's start by creating the relationship with the help of the Lookup Wizard.

We get values from another table: the Company Cars table.

We want to use the license number field, but we won't bother with a sort order because we're working with a small number of values.

Let's hide the key column, and call the field Company car.

In this case, we do NOT enable referential integrity—probably the only time we do this. This way, if we delete an employee, we don't accidentally delete a car.

Select Finish. Now we have a foreign key and an easy way to pick license numbers.

Next, make sure you have the field selected. Then select Fields, then Indexed, and Unique.

Now go to the Company Cars table and make sure the primary key is selected. You can see it's also indexed and unique, because it's an AutoNumber field, so we're done.

If you're in Design View, you can see the indexed property down here in the Field Properties pane.

If you open the list for the property, you see three values. You can use two of these values in a one-to-one relationship, either No or Yes (No Duplicates).

You've now created a one-to-one relationship in your database.

If we review the Relationships pane, you can see the relationship is shown as just a plain line without the 1 or infinity symbol which indicate the one-to-many or many-to-many relationships.

You can see that this database now has all three types of relationships.