Re: Enforcing Referential Integrity: Pros vs. Cons?

Richard Hollenbeck wrote:

> I'm not an ABSOLUTE newbie, but probably a relative newbie compared to
> many
> in this group. I've been programming in Access using VBA with DAO. Ever
> since I began, I thought it would ALWAYS been better to enforce
> referential integrity with cascading deletes and cascading updates to keep
> data
> synchronized among related tables. My thoughts were influenced by the
> idea that if a database were properly normalized, there should not be a
> reason
> why I shouldn't enforce this referential integrity. Am I correct or not?
> Are there any reasons why I would not? I'm assuming there is a reason
> because Access provides the option to enforce or not enforce. What are
> some very valid reasons why one would choose to not enforce? Is not
> enforcing just a cheap way to get around a poorly normalized database?

If I understand your basic idea, it is that a properly normalized database implies a cascading delete. While this is true in theory, in practice the question tends to be more about when to delete, and who is allowed to do it. It turns out a restrict RI is sometimes more appropriate.

For instance, if you want to delete a sales order, it stands to reason you will delete the header and the lines. A single action to the header which also deletes the lines is convenient and simple, no problem.

By the same token, if you delete a customer, then all of their sales orders and invoices should also go away, as otherwise you have orphans. But the key question is, do you really want to let just anyone walk in and delete a customer, with the corresponding disaster that might ensue?

For this reason, some people will divide RI into two groups. Some groups, like orders, they will do a cascade, and others, like customers, they will do a restrict. The idea is, if there are any orders for a customer, you should not be deleting the customer.

This whole arrangement also implies that there is some type of system-wide process that purges big sets of tables occasionally after moving information to history.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me