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. I think it's important enough to devote an entire chapter to the topic because I know that without referential integrity, all your beautifully built solutions will crumble and amount to nothing.
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 when you search 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 ...