5.2.1: One-To-Many

If you drag the PK field of one table to the other table, and if the FK does not have unique values, then you are creating a one-to-many relationship. MS Access will know and will display that fact for you. For each row in the referenced table there can be several related rows in the other table; that is, for a PK value there can be many rows in the other table with that value stored in the FK.

Example

Department and Course are related through the deptCode field. You can go through the exercise of creating the relationship between these two tables, but first you must remove the current relationship:

• • • delete the existing relationships line (click the line, press delete, and follow through with the dialog to delete the relationship).

Now, click and drag the deptCode field in Department and drop it on top of the deptCode field in Course. On releasing the mouse MS Access will present the following dialogue box:

At this point MS Access is requesting the user to confirm the proper fields are being related, and for the user to make a choice regarding Referential Integrity and on some ‘Cascade’ options – we do not discuss cascading in these notes. You should choose Enforce Referential Integrity in almost all cases as this helps reduce the chance of corrupting data.

For the above, when the user clicks Create, MS Access shows the relationships line with 1 on the one side and an infinity symbol on the many side of the relationship: