![]() You don't need a third (junction) table for this type of relationship. ![]() One-To-Many Relationship This is the most common relationship type. You create a many-to-many relationship by using a third table, called a junction table (more on that below). In our case, a single customer can order many products, and a single product could have many customers. A row in table A can have many matching rows in table B, and vice versa. These are as follows: Many-To-Many Relationship This is what our example above uses. There are three types of relationships that you can establish between tables. We just established a many-to-many relationship. Your table relationships should look like the ones in the following screenshot: That is, click and drag the ProductId from the Products table across to the corresponding ProductId field on the Orders table. Now do the same for the Products table.You will now see a line established between the CustomerId field on the Customers table and the CustomerId on the Orders table. Check that the values are the same as the following screenshot and click OK: Click Enforce Referential Integrity so that it is checked. The Edit Relationships dialog will pop up.Click and drag the CustomerId from the Customers table across to the corresponding CustomerId field on the Orders table: You will now see three boxes which represent your three tables.A Show Table dialog box will pop up, displaying all three tables.While viewing a table in Design view, and ensuring that the DESIGN tab is selected, click Relationships from the Ribbon:.Now it's time to create the relationship between all three tables. Now that you've created two extra tables, our database has three tables. ![]() If for some reason your table doesn't have a primary key set, or if you want to change the primary key field, you can simply right click on the field header (in Design view), and select Primary Key from the contextual menu. Access automatically sets the ID field as a primary key, so as long as you simply rename ID to ProductId (or OrderId as the case may be) it will already be a primary key. A table must have a primary key before a relationship can be established with another table. A primary key ensures that the data in this field is unique - no two values can be the same. ![]() Remember that the key icon indicates that the field is a primary key.
0 Comments
Leave a Reply. |