Microsoft Access Tips for xBase developers

Provided by Allen Browne, allenbrowne.com. Updated May 2006.

Referential Integrity can't be that easy!

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:

• adding an invoice with an invalid CustomerID,
• deleting a Customer who already has invoices,
• altering a CustomerID after invoices exist.

In an Access database, all this code is replaced by a single mouse click when you create the relationship:

1. Open the Relationships window. In Access 95 to 2003, choose Relationships from the Tools menu (Edit menu in Access 1 and 2). In Access 2007, choose Relationships on the Show/Hide group of the Database Tools ribbon.
2. Add both the Customer and Invoice tables to the Relationships window.
3. Drag the CustomerID field from the primary table (Customer) onto CustomerID in the related table (Invoice).
4. Access pops up a dialog box to confirm the relationship. Check the Referential Integrity checkbox.

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.)