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:
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.