Home > Articles > Data > Access

  • Print
  • + Share This
This chapter is from the book

Enforcing Referential Integrity

Database applications that work with multiple, related tables need to worry about enforcing referential integrity rules. These rules ensure that related tables remain in a consistent state relative to each other. In the sales leads application, for example, suppose the Companies table includes an entry for "ACME Coyote Supplies" and that the Contacts table contains three leads who work for ACME. What would happen if you deleted the ACME Coyote Supplies record from the Companies table? Well, the three records in the Contacts table would no longer be related to any record in the Companies table. Child records without corresponding records in the parent table are called, appropriately enough, orphans. This situation leaves your tables in an inconsistent state, which can have unpredictable consequences.

Preventing orphaned records is what is meant by enforcing referential integrity. You need to watch out for two situations:

  • Deleting a parent table record that has related records in a child table.

  • Adding a child table record that isn't related to a record in the parent table (either because the common field contains no value or because it contains a value that doesn't correspond to any record in the parent table).

  • + Share This
  • 🔖 Save To Your Account