How to define relationships between tables in an Access database

  • Article
  • 12 minutes to read
  • Applies to:
    Access 2013, Access 2010, Microsoft Office Admission 2007, Microsoft Office Access 2003

Note

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more data about this change, read this weblog mail.

Original KB number: 304466

Notation

Novice: Requires noesis of the user interface on single-user computers. This article applies only to a Microsoft Access database (.mdb or .accdb).

Summary

This article describes how to ascertain relationships in a Microsoft Access database. The commodity includes the following topics:

  • What are table relationships?
  • Kinds of table relationships
    • One-to-many relationships
    • Many-to-many relationships
    • One-to-one relationships
  • How to ascertain relationships betwixt tables
    • How to define a one-to-many or one-to-i relationship
    • How to define a many-to-many relationship
  • Referential integrity
  • Cascading updates and deletes
  • Join types

What are table relationships?

In a relational database, relationships enable yous to preclude redundant information. For instance, if you are designing a database that will track information about books, you might have a table named "Titles" that stores data about each volume, such as the book'southward title, appointment of publication, and publisher. There is also data that you might want to store about the publisher, such every bit the publisher'southward phone number, address, and ZIP Code/Postal Code. If y'all were to shop all this data in the "Titles" tabular array, the publisher's telephone number would be duplicated for each title that the publisher prints.

A meliorate solution is to store the publisher's information only one fourth dimension, in a separate table that we will phone call "Publishers." You would then put a pointer in the "Titles" table that references an entry in the "Publishers" table.

To make sure that you data stays synchronized, you tin enforce referential integrity between tables. Referential integrity relationships assist brand sure that information in one table matches information in another. For example, each title in the "Titles" table must exist associated with a specific publisher in the "Publishers" table. A title cannot be added to the database for a publisher that does not exist in the database.

Logical relationships in a database enable y'all to efficiently query information and create reports.

Kinds of table relationships

A human relationship works by matching data in key columns, usually columns (or fields) that take the aforementioned name in both tables. In most cases, the relationship connects the primary key, or the unique identifier column for each row, from one table to a field in another table. The cavalcade in the other table is known as the "strange primal." For example, if you want to track sales of each book championship, you create a relationship between the master key cavalcade (let's telephone call it title_ID) in the "Titles" table and a column in the "Sales" table that is named title_ID. The title_ID column in the "Sales" tabular array is the foreign key.

There are 3 kinds of relationships between tables. The kind of relationship that is created depends on how the related columns are defined.

One-to-many relationships

A i-to-many relationship is the well-nigh mutual kind of relationship. In this kind of human relationship, a row in table A can take many matching rows in table B. But a row in tabular array B can accept merely i matching row in table A. For example, the "Publishers" and "Titles" tables take a one-to-many relationship. That is, each publisher produces many titles. But each title comes from simply one publisher.

A one-to-many relationship is created if but one of the related columns is a primary key or has a unique constraint.

In the relationship window in Admission, the principal central side of a one-to-many relationship is denoted by a number 1. The foreign central side of a relationship is denoted by an infinity symbol.

Screenshot of an example for one-to-many relationships in the relationships window in Access.

Many-to-many relationships

In a many-to-many relationship, a row in tabular array A can have many matching rows in table B, and vice versa. You create such a relationship past defining a third tabular array that is called a junction table. The chief key of the junction table consists of the foreign keys from both tabular array A and table B. For example, the "Authors" table and the "Titles" tabular array have a many-to-many relationship that is defined past a one-to-many relationship from each of these tables to the "TitleAuthors" table. The primary key of the "TitleAuthors" tabular array is the combination of the au_ID column (the "Authors" table's primary central) and the title_ID column (the "Titles" table's master key).

Screenshot of an example for many-to-many relationships in the relationships window in Access.

One-to-one relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are principal keys or take unique constraints.

This kind of human relationship is non mutual, because most data that is related in this fashion would be in i table. Y'all might utilize a 1-to-one relationship to take the following actions:

  • Divide a tabular array with many columns.
  • Isolate part of a table for security reasons.
  • Store information that is short-lived and could be easily deleted past deleting the tabular array.
  • Store information that applies only to a subset of the main tabular array.

In Admission, the primary cardinal side of a one-to-i human relationship is denoted past a central symbol. The strange key side is also denoted past a central symbol.

How to ascertain relationships between tables

When you create a human relationship betwixt tables, the related fields do non have to have the aforementioned names. However, related fields must have the same data blazon unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if theFieldSizeproperty of both of the matching fields is the same. For example, yous tin match an AutoNumber field and a Number field if theFieldSizeproperty of both fields isLong Integer. Even when both matching fields are Number fields, they must have the sameFieldSizeproperty setting.

How to define a i-to-many or one-to-ane relationship

To create a one-to-many or a one-to-one relationship, follow these steps:

  1. Close all tables. Yous cannot create or change relationships between open up tables.

  2. In Admission 2002 or Access 2003, follow these steps:

    1. Printing F11 to switch to the Database window.
    2. On the Tools card, click Relationships.

    In Access 2007, Access 2010, or Admission 2013, click Relationships in the Show/Hide group on the Database Tools tab.

  3. If yous have not nevertheless defined any relationships in your database, the Evidence Table dialog box is automatically displayed. If y'all want to add the tables that you lot want to relate but the Show Table dialog box does not announced, click Evidence Table on the Relationships menu.

  4. Double-click the names of the tables that you lot desire to relate, and then close the Evidence Table dialog box. To create a human relationship between a table and itself, add that tabular array two times.

  5. Drag the field that y'all want to relate from ane table to the related field in the other table. To elevate multiple fields, press Ctrl, click each field, and then drag them.

    In most cases, you drag the principal key field (this field is displayed in bold text) from one tabular array to a like field (this field frequently has the same proper name) that is chosen the foreign key in the other tabular array.

  6. The Edit Relationships dialog box appears. Make certain that the field names that are displayed in the ii columns are correct. Yous can change the names if it is necessary.

    Set the relationship options if it is necessary. If you have to have information virtually a specific particular in the Edit Relationships dialog box, click the question marker button, then click the detail. (These options will be explained in detail later on in this commodity.)

  7. Click Create to create the human relationship.

  8. Repeat steps 4 through vii for each pair of tables that you want to chronicle.

    When you close the Edit Relationships dialog box, Access asks whether you lot want to save the layout. Whether y'all save the layout or practice non relieve the layout, the relationships that you create are saved in the database.

    Note

    Y'all tin create relationships not simply in tables but also in queries. However, referential integrity is non enforced with queries.

How to define a many-to-many relationship

To create a many-to-many relationship, follow these steps:

  1. Create the 2 tables that will take a many-to-many relationship.

  2. Create a 3rd table. This is the junction table. In the junction table, add new fields that take the same definitions as the primary fundamental fields from each tabular array that you created in step 1. In the junction table, the primary key fields function equally foreign keys. You can add other fields to the junction table, just as yous tin to whatever other table.

  3. In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in a "TitleAuthors" junction tabular array, the primary key would be made upwardly of the OrderID and ProductID fields.

    Note

    To create a main key, follow these steps:

    1. Open up a table in Design view.

    2. Select the field or fields that yous want to define every bit the primary key. To select one field, click the row selector for the desired field. To select multiple fields, hold downwards the Ctrl primal, and and then click the row selector for each field.

    3. In Access 2002 or in Access 2003, click Chief Cardinal on the toolbar.

      In Access 2007, click Primary Key in the Tools group on the Design tab.

      Note

      If you want the society of the fields in a multiple-field primary key to differ from the club of those fields in the tabular array, click Indexes on the toolbar to display the Indexes dialog box, and and so reorder the field names for the alphabetize named PrimaryKey.

  4. Define a one-to-many human relationship between each chief table and the junction tabular array.

Referential integrity

Referential integrity is a organisation of rules that Access uses to make certain that relationships between records in related tables are valid, and that you lot do non accidentally delete or alter related data. You tin can set referential integrity when all the post-obit conditions are true:

  • The matching field from the primary table is a principal key or has a unique alphabetize.
  • The related fields have the aforementioned data type. There are two exceptions. An AutoNumber field can be related to a Number field that has a FieldSize property setting of Long Integer, and an AutoNumber field that has a FieldSize property setting of Replication ID can exist related to a Number field that has a FieldSize holding setting of Replication ID.
  • Both tables belong to the same Access database. If the tables are linked tables, they must be tables in Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity cannot be enforced for linked tables from databases in other formats.

The following rules apply when y'all use referential integrity:

  • You cannot enter a value in the foreign central field of the related table that does not exist in the primary key of the primary table. Nevertheless, you can enter a Zip value in the foreign fundamental. This specifies that the records are unrelated. For instance, you cannot accept an social club that is assigned to a customer who does not exist. Withal, y'all can have an order that is assigned to no one past entering a Null value in the CustomerID field.
  • You cannot delete a tape from a main tabular array if matching records exist in a related table. For example, yous cannot delete an employee record from the "Employees" table if there are orders assigned to the employee in the "Orders" table.
  • You cannot change a primary key value in the primary table if that record has related records. For example, yous cannot change an employee'southward ID in the "Employees" table if there are orders assigned to that employee in the "Orders" tabular array.

Cascading updates and deletes

For relationships in which referential integrity is enforced, you can specify whether you want Access to automatically pour update or cascade delete related records. If you set these options, delete and update operations that would normally be prevented past referential integrity rules are enabled. When you lot delete records or modify primary primal values in a primary table, Access makes the necessary changes to related tables to preserve referential integrity.

If you click to select the Cascade Update Related Fields check box when you define a relationship, any fourth dimension that you change the primary fundamental of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For instance, if you lot change a customer's ID in the "Customers" table, the CustomerID field in the "Orders" tabular array is automatically updated for every one of that customer'due south orders then that the relationship is non cleaved. Admission cascades updates without displaying whatsoever bulletin.

Note

If the primary cardinal in the primary table is an AutoNumber field, selecting the Cascade Update Related Fields check box has no effect considering you cannot change the value in an AutoNumber field.

If y'all select the Cascade Delete Related Records check box when you lot define a relationship, any time that you lot delete records in the primary table, Admission automatically deletes related records in the related tabular array. For example, if you delete a customer record from the "Customers" table, all the customer's orders are automatically deleted from the "Orders" tabular array. (This includes records in the "Order Details" table that are related to the "Orders" records). When yous delete records from a form or datasheet when the Cascade Delete Related Records bank check box selected, Access warns you that related records may as well exist deleted. However, when y'all delete records by using a delete query, Admission automatically deletes the records in related tables without displaying a warning.

Join types

There are three join types. Yous tin can see them in the following screen shot:

Screenshot of Join Properties, which shows three join types.

Option 1 defines an inner bring together. An inner join is a join in which records from ii tables are combined in a query's results simply if values in the joined fields meet a specified status. In a query, the default join is an inner join that selects records only if values in the joined fields match.

Option two defines a left outer bring together. A left outer join is a bring together in which all the records from the left side of the LEFT Bring together operation in the query's SQL argument are added to the query's results, fifty-fifty if there are no matching values in the joined field from the tabular array on the right side.

Choice three defines a correct outer join. A right outer bring together is a join in which all the records from the right side of the Right Join operation in the query'southward SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left side.