Can this relationship be saved

Define relationships between tables in an Access database

  • 11 minutes to read
  • Applies to:
    Access 2013, Access 2010, Microsoft Office Access 2007, Microsoft Office Access 2003

Note

Office 365 ProPlus is in Microsoft 365 apps for business renamed. Please see this blog post for more information on this change.

This article describes table relationships and how to define relationships in a Microsoft Access database.

Original KB number:   304466

Note

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

Summary

This article describes how to define relationships in a Microsoft Access database. The article contains the following topics:

  • What are table relationships?
  • Types of table relationships
    • 1: n relationships
    • many-to-many relationships
    • 1: 1 relationships
  • Define relationships between tables
    • Define a one-to-many or one-to-one 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, you can use relationships to prevent redundant data. For example, if you're designing a database that tracks information about books, you may have a table called Title that stores information about each book, such as: B. the title of the book, the date of publication and the publisher. There is also information that you want to save about the publisher, such as: B. Publisher's phone number, address, and zip code. If you save all of this information in the Titles table, the publisher's phone number will be duplicated for every title the publisher prints.

A better solution is to store the publisher's information only once in a separate table that we call the “publisher”. In the "Title" table, add a reference to an entry in the "Publisher" table.

To ensure that your data stays in sync, you can enforce referential integrity between tables. Referential integrity relationships ensure that information in one table matches information in another table. For example, each title in the Titles table must be associated with a specific publisher in the Publishers table. A title for a publisher that does not exist in the database cannot be added to the database.

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

Types of table relationships

In a relationship, data in key columns, usually columns (or fields) that have the same name in both tables, belong together. In most cases, the relationship links the primary key or column with the unique identifier for each row in one table to a field in another table. The column in the other table is called the "Foreign Key". If you z. For example, to track the sales of each book title, create a relationship between the primary key column (let's call ittitle_ID) in the "Title" table and a column in the "Sales" table that also contains the name title_ID gets. The gap title_ID in the "Sales" table is the foreign key.

There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.

1: n relationships

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many related rows in table B. However, a row in Table B can only have one associated row in Table A. For example, the "Publisher" and "Title" tables have a one-to-many relationship. That is, each publisher produces many titles. But each title is from only one publisher.

A one-to-many relationship is created when only one of the related columns is a primary key or contains a unique constraint.

In the Relationships window in Access, the number 1 denotes the primary key side of a one-to-many relationship. The foreign key side of a relationship is denoted by an infinity symbol.

many-to-many relationships

In a many-to-many relationship, a row in table A can contain many matching rows in table B, and vice versa. You create such a relationship by defining a third table called a join table. The primary key of the join table consists of the foreign keys from Table A and Table B. For example, the Authors table and the Titles table have a many-to-many relationship that results from a one-to-many relationship between each of these tables and the table "TitleAutors" is defined. The primary key of the "TitleAutors" table is a combination of the columnau_ID (Primary key of the table "Authors") and the column ** title_ID ** (primary key of the table "Title").

1: 1 relationships

In a one-to-one relationship, a row in table A cannot have more than one associated row in table B, and vice versa. A one-to-one relationship is created when both related columns are primary keys or have unique constraints.

This type of relationship is not common because most of the information related in this way would be contained in a single table. Using a one-to-one relationship, you can do the following:

  • Split a table with many columns.
  • Isolate part of a table for security reasons.
  • Stores ephemeral data that could easily be deleted by deleting the table.
  • Stores information that only applies to a subset of the main table.

In Access, the primary key side of a one-to-one relationship is identified by a key symbol. The foreign key side is also identified by a key symbol.

Define relationships between tables

When you create a relationship between tables, the related fields don't have to have the same names. However, linked fields must be of the same data type unless the primary key field is an AutoNumber field. You can only match an AutoNumber field with a Number field if the FieldSize property of the two related fields is identical. You can e.g. For example, align an AutoNumber field and a number field if the FieldSize property of both fields is of the type "Long Integer". Even if both related fields are number fields, they must have the same FieldSize property setting.

Define a one-to-many or one-to-one relationship

To create a one-to-many or one-to-one relationship, do the following:

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

  2. In Access 2002 or 2003, do the following:

    1. Press F11 to switch to the database window.
    2. Do you click in the menu Extras on Relationships.

    In Access 2007, Access 2010, or Access 2013, click the tab Database tools in the group Show / hide onRelationships.

  3. If you have not yet defined any relationships in your database, the dialog box appears automatically Show table. When you are ready to add the tables that you want to link, the dialog box Show table but does not appear, click the menu Relationships onShow table.

  4. Double-click the names of the tables you want to link, then close the dialog box Show table. To create a relationship between a table and itself, add that table twice.

  5. Drag the field you want to relate from one table onto the corresponding field in the other table. To drag multiple fields, hold down Ctrl, click each field, and then drag them.

    In most cases, you will drag the primary key field (this field is shown in bold) from one table to a similar field (this field often has the same name) called a foreign key in the other table.

  6. The dialog box Edit relationships is displayed. Make sure the field names that appear in the two columns are correct. You can change the names if necessary.

    Set the relationship options if necessary. If you have information about a specific item in the dialog boxEdit relationships click the question mark button, and then click the item. (These options are discussed in detail later in this article.)

  7. click on Createto create the relationship.

  8. Repeat steps 4 through 7 for each pair of tables that you want to relate.

    When you get the Edit relationships Close, Access asks if you want to save the layout. Regardless of whether you save the layout or not, the relationships you create are saved in the database.

    Note

    You can create relationships not only in tables but also in queries. However, referential integrity is not enforced with queries.

How to define a many-to-many relationship

To create a many-to-many relationship, do the following:

  1. Create the two tables that you want to have a many-to-many relationship.

  2. Create a third table. This is the link table. In the join table, add new fields that have the same definitions as the primary key fields from each table you created in step 1. In the link table, the primary key fields act as foreign keys. You can add additional fields to the join table just as you can in any other table.

  3. In the join table, set the primary key to include the primary key fields from the other two tables. For example, in a "TitleAuthor" link table, the primary key would be from the fields Order ID and ProductID consist.

    Note

    To create a primary key, do the following:

    1. Open a table in Design view.

    2. Select the field or fields that you want to define as the primary key. To select a field, click the row selector for the field you want. To select multiple fields, hold down the Ctrl key, then click the row selector for each field.

    3. In Access 2002 or Access 2003, click on the toolbar Primary key.

      In Access 2007, click the tab design in the group Extras on Primary key.

      Note

      To change the order of the fields in a multi-field primary key from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then rearrange the field names for the index named PrimaryKey at.

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

Referential Integrity

Referential integrity is a set of rules that Access uses to ensure that relationships between records in related tables are valid and that you do not accidentally delete or change related data. You can set referential integrity when all of the following conditions are true:

  • The field to be linked from the primary table is a primary key or has a unique index.
  • The related fields have the same data type. There are two exceptions to this rule: One AutoNumber-Field can be with a NumberField that has "Long Integer" as the property setting; and a AutoNumberField that has a Replication ID as the property setting can be matched with a NumberField that has a Replication ID as the property setting.
  • Both tables belong to the same Access database. If the tables are linked, they must be Access format tables and you must open the database where they are stored to set referential integrity. Referential integrity cannot be enforced on linked tables from databases in other formats.

The following rules apply when using referential integrity:

  • You cannot enter a value in the linked table's foreign key field that does not exist in the primary key of the primary table. However, you can enter a null value in the foreign key. This determines that the records are not linked. You can e.g. For example, don't have an order assigned to a customer who doesn't exist. However, you can have an order that is not assigned to anyone by entering a zero value in the field CustomerID enter.
  • You cannot delete a record from a primary table if there are related records in a related table. You can e.g. For example, you cannot delete an employee record from the "Employees" table if the employee has orders assigned in the "Orders" table.
  • You cannot change a primary key value in the primary table if that record contains related records. You can e.g. For example, do not change the ID of an employee in the "Employees" table if orders are assigned to this employee in the "Orders" table.

Cascading updates and deletes

For relationships that enforce referential integrity, you can specify whether Access should automatically cascade or delete the related records. Setting these options enables delete and update operations that would normally be prevented by referential integrity rules. When you delete records or change primary key values ​​in a primary table, Access makes the necessary changes to related tables to maintain referential integrity.

If you clicked on the check box when defining a relationship Propagation of updates to related fields If you click, Microsoft Access automatically updates the primary key in all linked records to the new value when you change the primary key of a record in the primary table. For example, if you change a customer's ID in the Customers table, the CustomerID automatically updated in the "Orders" table for each order from this customer, so that the relationship is not broken. Access cascade updates without displaying a message.

Note

If the primary key in the primary table is an AutoNumber field, has the option Propagation of updates to related fields no effect because you cannot change the value in an AutoNumber field.

If you checked the box when defining a relationship Deletion transfer to related records Enable, every time you delete records in the primary table, Access automatically deletes the related records in the related table. For example, if you delete a customer record from the Customers table, all of the customer's orders are automatically deleted from the Orders table. (This includes records in the Order Details table that relate to the Orders records). If you delete records from a form or datasheet while the check boxDeletion transfer to related records When enabled, Access warns you that linked records may also be deleted. However, if you delete records using a delete query, Access automatically deletes the records in related tables without displaying a warning.

Join types

There are three primary join types: you can see them in the following screenshot:

Option 1 defines an inner join.An inner join is a join in which records from two tables are combined in the results of a query only if the values ​​in the related fields meet a certain condition. In a query, the default join is an inner join that selects records only if the values ​​in the related fields match.

Option 2 defines a left outer join. A left outer join is a join in which all records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the results of the query, even if there are no matching values ​​in the related field from the table on the right Side are present.

Option 3 defines a right outer join. A right outer join is a join that adds all the records from the right side of the RIGHT JOIN operation in the query's SQL statement to the results of the query, even if there are no matching values ​​in the related field from the table on the left Side are present.