Learning relationship types and rules in Microsoft Access 2019

In the following bullet list, you will find the three types ofrelationships we can set from one table to another in a database. Themost common of these relationship types is the one-to-many relationship.When we drag the primary key to create a relationship from one table tothe other, the relationship is determined automatically by the datacontained in the tables and defined by the primary key:

  • One to many: A field in one table can have manyidentical fields in the second table, but a field in the second tablehas only one identical field in the first table (this relationship typeis the most common where the field in the first table is the table’sprimary key):
    • Rule: The relationship can only be created ifone of the related fields is a primary key or a uniqueindex.
  • One to one: A field in the first table can haveonly one identical field in another table and vice versa. Thisrelationship is not a common one:
    • Rule: This relationship is only created ifboth related fields are primary keys or have a unique index.
  • Many to many: A field in one table can have manyidentical fields in another table, and so on. For example, one order canhave many products, with each product appearing on many orders. Ajunction table a third table is normally required when using thisrelationship. So, it  contains the primary keys from both tables:
    • Rule: It must have two one-to-manyrelationships, including a third table whose primary key consists of twofields (one from each table).

The following screenshot displays the tables within a databasein the relationship window:

ms office 658

This is where you can edit relationships between tables byright-clicking on the lines in between each table connection. In thenext section, we will learn how to define a primary key so thatrelationships can be formed between tables.

Leave a Comment