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

Role 365 ProPlus is existence renamed to Microsoft 365 Apps for enterprise. For more than information virtually this change, read this blog post.

Original KB number: 304466

Note

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

Summary

This article describes how to define relationships in a Microsoft Access database. The article 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 define relationships between tables
    • How to define a 1-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 you to prevent redundant data. For example, if you are designing a database that will track information virtually books, you might accept a table named "Titles" that stores information about each book, such as the book'due south title, engagement of publication, and publisher. There is as well data that y'all might want to shop about the publisher, such as the publisher's telephone number, accost, and ZIP Code/Postal Code. If you were to shop all this data in the "Titles" table, the publisher's telephone number would be duplicated for each championship that the publisher prints.

A improve solution is to shop the publisher'southward data only one time, in a split up table that we volition call "Publishers." You would then put a pointer in the "Titles" tabular array that references an entry in the "Publishers" table.

To make certain that you data stays synchronized, you can enforce referential integrity betwixt tables. Referential integrity relationships help brand sure that data in i table matches information in another. For instance, each championship in the "Titles" table must be associated with a specific publisher in the "Publishers" table. A championship cannot be added to the database for a publisher that does not exist in the database.

Logical relationships in a database enable you to efficiently query data and create reports.

Kinds of tabular array relationships

A relationship works by matching data in key columns, usually columns (or fields) that have the same name in both tables. In most cases, the relationship connects the chief central, or the unique identifier column for each row, from ane tabular array to a field in another table. The column in the other table is known as the "foreign key." For example, if you desire to track sales of each book championship, yous create a relationship between the principal fundamental cavalcade (permit's telephone call information technology 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" table is the strange key.

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

I-to-many relationships

A one-to-many relationship is the most mutual kind of relationship. In this kind of relationship, a row in tabular array A tin can have many matching rows in table B. But a row in table B tin have merely one matching row in table A. For case, the "Publishers" and "Titles" tables have a ane-to-many human relationship. That is, each publisher produces many titles. But each title comes from only i publisher.

A 1-to-many relationship is created if only one of the related columns is a main fundamental or has a unique constraint.

In the relationship window in Admission, the principal key side of a one-to-many human relationship is denoted by a number 1. The foreign key 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 tin can have many matching rows in tabular array B, and vice versa. You lot create such a relationship past defining a third table that is called a junction table. The primary cardinal of the junction table consists of the foreign keys from both table A and tabular array B. For example, the "Authors" table and the "Titles" tabular array take a many-to-many relationship that is divers by a one-to-many relationship from each of these tables to the "TitleAuthors" tabular array. The primary key of the "TitleAuthors" tabular array is the combination of the au_ID column (the "Authors" table'south principal key) and the title_ID column (the "Titles" tabular array's primary primal).

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

One-to-one relationships

In a ane-to-one relationship, a row in table A tin have no more than i matching row in table B, and vice versa. A 1-to-one relationship is created if both of the related columns are chief keys or accept unique constraints.

This kind of human relationship is not common, considering most data that is related in this manner would be in 1 table. You might utilise a one-to-one human relationship to accept the following actions:

  • Carve up a table with many columns.
  • Isolate office of a table for security reasons.
  • Store information that is short-lived and could be easily deleted by deleting the table.
  • Shop data that applies only to a subset of the main table.

In Access, the chief primal side of a one-to-one relationship is denoted by a key symbol. The strange key side is likewise denoted past a key symbol.

How to define relationships between tables

When you create a human relationship between tables, the related fields exercise not accept to accept the same names. Withal, related fields must have the same information blazon unless the master key field is an AutoNumber field. You can match an AutoNumber field with a Number field merely if theFieldSizeproperty of both of the matching fields is the aforementioned. For instance, you can friction 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 ascertain a ane-to-many or one-to-one relationship

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

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

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

    1. Press F11 to switch to the Database window.
    2. On the Tools menu, click Relationships.

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

  3. If yous accept not notwithstanding defined any relationships in your database, the Show Tabular array dialog box is automatically displayed. If you want to add together the tables that you want to relate merely the Testify Table dialog box does not appear, click Evidence Table on the Relationships menu.

  4. Double-click the names of the tables that you want to relate, and then shut the Prove Tabular array dialog box. To create a relationship between a table and itself, add together that tabular array two times.

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

    In well-nigh cases, y'all drag the principal central field (this field is displayed in bold text) from one tabular array to a similar field (this field ofttimes has the aforementioned name) that is chosen the foreign central in the other table.

  6. The Edit Relationships dialog box appears. Make sure that the field names that are displayed in the two columns are correct. You tin change the names if information technology is necessary.

    Prepare the relationship options if it is necessary. If you take to have information about a specific detail in the Edit Relationships dialog box, click the question marker push, and then click the item. (These options volition be explained in item later on in this article.)

  7. Click Create to create the relationship.

  8. Repeat steps iv through 7 for each pair of tables that yous want to relate.

    When yous shut the Edit Relationships dialog box, Access asks whether you want to salve the layout. Whether y'all relieve the layout or do not save the layout, the relationships that yous create are saved in the database.

    Note

    You tin create relationships non only in tables just also in queries. Withal, referential integrity is not enforced with queries.

How to define a many-to-many relationship

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

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

  2. Create a third table. This is the junction table. In the junction table, add new fields that have the same definitions equally the primary key fields from each table that you created in stride 1. In the junction table, the primary cardinal fields role as strange keys. You tin add other fields to the junction table, just as you tin can to any other table.

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

    Note

    To create a primary key, follow these steps:

    1. Open a table in Pattern view.

    2. Select the field or fields that you desire to define as the primary key. To select 1 field, click the row selector for the desired field. To select multiple fields, concur down the Ctrl key, and then click the row selector for each field.

    3. In Admission 2002 or in Access 2003, click Primary Key on the toolbar.

      In Access 2007, click Primary Fundamental in the Tools grouping on the Blueprint tab.

      Note

      If you desire the order of the fields in a multiple-field chief primal to differ from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and and so reorder the field names for the index named PrimaryKey.

  4. Define a one-to-many relationship between each primary table and the junction table.

Referential integrity

Referential integrity is a system of rules that Access uses to make sure that relationships between records in related tables are valid, and that you exercise not accidentally delete or modify related data. You can set referential integrity when all the post-obit conditions are truthful:

  • The matching field from the primary table is a primary central or has a unique index.
  • The related fields have the same information type. There are two exceptions. An AutoNumber field tin 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 be related to a Number field that has a FieldSize belongings setting of Replication ID.
  • Both tables vest to the aforementioned Admission database. If the tables are linked tables, they must be tables in Access format, and you lot must open the database in which they are stored to set up referential integrity. Referential integrity cannot be enforced for linked tables from databases in other formats.

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

  • You lot cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Nil value in the foreign key. This specifies that the records are unrelated. For example, y'all cannot have an club that is assigned to a client who does not exist. However, you can take an lodge that is assigned to no one by inbound a Null value in the CustomerID field.
  • You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee tape from the "Employees" table if there are orders assigned to the employee in the "Orders" tabular array.
  • You cannot change a primary key value in the primary table if that tape has related records. For example, yous cannot change an employee's ID in the "Employees" tabular array if at that place are orders assigned to that employee in the "Orders" table.

Cascading updates and deletes

For relationships in which referential integrity is enforced, y'all can specify whether you want Access to automatically cascade update or pour delete related records. If you ready these options, delete and update operations that would usually be prevented by referential integrity rules are enabled. When yous delete records or modify primary cardinal values in a primary table, Admission makes the necessary changes to related tables to preserve referential integrity.

If you click to select the Pour Update Related Fields cheque box when you define a relationship, any time that yous change the primary primal of a tape in the chief tabular array, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you alter a client's ID in the "Customers" table, the CustomerID field in the "Orders" table is automatically updated for every one of that customer'due south orders then that the relationship is not broken. Access cascades updates without displaying any message.

Note

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

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

Bring together types

In that location are iii join types. You tin can see them in the post-obit screen shot:

Screenshot of Join Properties, which shows three join types.

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

Selection 2 defines a left outer join. A left outer join is a bring together in which all the records from the left side of the LEFT JOIN operation in the query'south SQL statement are added to the query'south results, even if at that place are no matching values in the joined field from the table on the right side.

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