So far this chapter has discussed individual table objects in isolation. With Microsoft Jet, you can use Relation objects to define how tables relate to one another. By creating relationships between tables, you can ensure that the data in your tables remains logically consistent. You can, for example, make sure that no orders are taken for nonexistent customers. You can prevent someone from deleting an employee record if there are corresponding entries in a Tasks table.
The rules that govern relationships between tables are known as referential integrity constraints. For relationships in which referential integrity is enforced, you can specify whether you want cascading updates and cascading deletes to be automatically performed on related records. If you enable cascading updates, changing a record in the primary table causes the change to be automatically made to all related records in the related table or tables. If you enable cascading deletes, deleting a record in the primary table causes the deletion of all related records in the related table or tables.
In addition to flagging attempts to violate referential integrity as errors, with Microsoft Jet you can specify what action should be taken when an attempt occurs. For example, by using cascading deletes, your application can automatically delete all of the related task’s entries when you delete an employee record. Cascading updates can be used to change foreign key values in related tables when the primary key of a primary table is changed (a foreign key is a field that refers to the primary key field in another table).
After you establish the referential integrity rules governing the relationships between the tables in your database, the burden of enforcing these rules is removed from your application’s code. You don’t have to check explicitly for conditions that violate referential integrity. Microsoft Jet informs you when a violation attempt occurs.
So, whether your database program is updated by a user modifying the tables from within Microsoft Access, or by an ODBC call from a custom third-party application, the logical consistency of the data in your tables is preserved.