Setting up referential integrity in Microsoft Access 2019

Referential integrity ensures that records that are related betweentables in a database are bound by monitoring the manipulation of datastored in the related tables.

This relationship can only be enforced under the followingconditions:

  • Related fields need to have the same data type.
  • Both fields must belong to the same database.
  • The matching field from the primary table is assigned as the primarykey or is defined as a unique index.

How will referential integrity change my database?

  • If the value in a matching field already exists as the matchingfield of the related table, then a record (row) can only be added to therelated table.
  • If any matching records exist in a related table, then recordscannot be deleted from the primary table. An exception to this would bewhen the Cascade Delete RelatedRecords option is selected.
  • The matching field in the primary table cannot be modified if anymatching records exist in the related tables unless the Cascade Update Related Fields option isselected.

Setting up referential integrity is done as follows:

  1. Click on Database Tools |Relationships.
  2. Select the line between the two relatedtables you wish to edit the relationship of. Click onthe EditRelationships icon on the ribbon or right-click onthe line between the two related tables and choose Edit Relationship….
  3. Locate the Enforce ReferentialIntegrity checkbox and click on it to select it:
ms office 761
  1. Click on OK to commit to thechange.

Leave a Comment