In Microsoft Access there is an entry in the ‘Database Tools’ Menu called Relationships. It should be used to maintain the integrity of the relations in your database.
Contact us here for more help.
Previous discussions have discussed the master tables and subordinate tables. The more common terminology is either ‘master/detail’ or ‘parent/child’. For this discussion, we will use parent/child.
You can create the rules that Access will use to maintain the integrity of the relationships between your parent/child tables by using the relationships screen.
In the diagram (above) there is a parent/child relationship between Author and Books. The field called AuthorID links these tables.
Enforce Referential Integrity
When creating the link you will want to mark the ‘Enforce Referential Integrity’ check box and the ‘Cascade Delete Related Records’.
By checking the ‘Enforce Referential Integrity’ setting, the insertion of ‘orphaned’ records is prevented. An orphaned record is defined as having a child record without a matching parent record.
Cascade Delete Related Records
In a very similar fashion, the ‘Cascade Delete Related Records’ option prevents orphaned records when you delete a master record. When this box is checked, Microsoft Access will automatically delete the associated child records.
All relational database management systems have this automatic capability option.
Cascade Update Related Fields
This check box is for those Access users who use actual data to link two tables. You should only need to check this on the rare occasion when the ID field is an internally generated integer. An example is an Employee_ID that is equivalent to an autonumber field.
Join Type
When setting up a relationship there is a button for ‘Join Type’. The Join Properties popup has three choices.
Inner Joins
The first choice is ‘Only include rows where the join fields in both tables are equal’. This is the choice that you will most likely pick 99% of the time. One way to think of this is that you would not want to put a foreign key into a child table if you didn’t need it for all records.
Outer Joins
In those few cases where you just won’t have a value for the foreign key in the child table you will need to use the 2nd or 3rd option in the Join Properties popup. This option is usually referred to as an ‘outer join’. This tells Access to get all the records from the parent table and to get any records that happen to have a matching key in the child table.
