FileMaker® Pro 9 Bible

Chapter 15. Maintaining Referential Integrity

Referential integrity, an essential concept, lies at the very heart of relational database development. Ideally, the end users of your solutions take referential integrity for granted — and you, as developer, place it before many other considerations. We think it's important enough to devote an entire chapter to the topic because we know that without referential integrity, all your beautifully built solutions will crumble and come to nothing.

Pinpointing Common Causes of Referential Integrity Problems

Referential integrity has many dimensions, the problem of orphaned records foremost among them. Consider, for a moment, an invoicing system where you have an invoice table and a line items table storing the items for each invoice; however, an invoice has been deleted without the corresponding line item records being deleted. Now, when you perform a search, you get a different result searching in the Invoices table than when you search in the Line Items table. The difference arises because the deleted invoice's items remain in the Line Items table, so they appear there, but no related record exists in the Invoices table, so they don't appear when you search there.

When you get different results searching in different places, you no longer know what is correct, and you have a lot of work to do to determine the discrepancy's cause. This example is one of several common problems affecting referential integrity. Three of the most common causes ...