Provided by Allen Browne, firstname.lastname@example.org. Updated May 2006.
A Customer table is often be related to an Invoice table, on a CustomerID field. To prevent referential integrity problems, database programmers had to write code for every input screen to prevent situations like:
In an Access database, all this code is replaced by a single mouse click when you create the relationship:
That's it! You never worry about referential integrity problems between these tables again.
In the same dialog box, are options for Cascading Update, and Cascading Delete. If you elect to check these boxes, referential integrity is maintained by updating or deleting data in the related table. For example, if you alter a Customer's ID, then all related records are updated to reflect the change.
Referential Integrity is one of Access' strong points, but there is one situation you should consider. A record added to the Invoice table with a blank CustomerID does not violate referential integrity. If you want to use a blank CustomerID (e.g. for a "Cash Sale"), make sure you understand about outer joins or records may appear to slip through the cracks.
Alternatively, block the possibility of a blank CustomerID by opening the Invoice table in Design View and setting its Required property of this field to "Yes". That's another Access mouse click that will save you many lines of validation code! (Not available in Access 1.)
|Home||Index of tips||Top|