5.2: Relationships

Two tables can be related through one-to-one, one-to-many, or many-to-many relationships. If you open the Relationships Tool for the University database, you will see the following diagram showing two tables and one relationship:

There are two labels on the line which inform us the relationship is one-to-many for which there are two rules that are in place:

• • • for each department there will be zero or more courses for that department, and,
    • each course is for exactly one department.

To create a relationship in MS Access you must:

• • • open the Relationships Tool
    • add the pertinent tables to the diagram if they are not there already
    • click, hold, and drag a field (normally this is the PK) of one table to the related field (to become an FK) in the other table.

You will be asked whether or not Referential Integrity is to be enforced. As a general rule- of-thumb, you should select Yes – there must be some exceptional circumstance that makes you select No.

Once relationships are established using the Relationships Tool they are used by MS Access when you create queries – the relationships are used as the default for table joins.