[AccessD] 2 Questions

JC, 

Here, here!

A very good explanation which should be a FAQ somewhere.  

-- 
Stuart

On 10 Mar 2010 at 8:34, jwcolby wrote:

> Tony,
> 
> Referential integrity is the foundation of a database.  Without it you are hopelessly lost.
> 
> 1) Referential integrity essentially says that you can't have a child without having a parent (makes 
> sense right?).
> 
> So... If you have clients and orders, you can't have an order without having a client.  More 
> importantly, you can't have an order without selecting a SPECIFIC client.  Referential integrity 
> says that you can't DELETE a client without deleting all of that client's orders.  Again, makes 
> sense right?  If you are allowed to delete clients without deleting that client's orders then you 
> have orders with no clients (see one above).  This just hoses everything.  You do reports of all 
> your orders and where they shipped to... ooops these orders don't have a client so we can't tell 
> where we shipped them to.  We count orders not paid for yet.  Ooops we have orders with no client so 
> there is no way to determine that they were paid for.  ANYTHING that you have to have the Client 
> record to report on gets trashed.
> 
> It's just a bad thing, having orders with no client.  Or any other child record without a parent.
> 
> This condition is called "orphaned records" because a child with no parent is an orphan.  Orphans 
> are "lost", they just cause a raft of issues, all of them BAD!
> 
> Referential integrity is the business of the database engine, NOT the FE or application.  Every 
> database engine, whether Jet, SQL Server, Oracle or any other has powerful mechanisms built in for 
> maintaining referential integrity.
> 
> I mentioned that RE is not the business of the FE, and the reason is simple.  If you have to handle 
> RE then you are CONSTANTLY checking in code whether you performed all of the steps necessary to 
> maintain RE.  Furthermore, you place that same burden on any other developer accessing your data. 
> Suddenly ANY developer trying to update, add or delete records has to be concerned with RE.  Most of 
> us developers simply are not trained to do RE right, and even if we are, human error creeps in. 
> Even worse, I EXPECT that you will use RE, EVERYONE USES RE.  So if you don't, and I expect that you 
> are, I don't bother to even attempt to handle RE issues and suddenly I am trashing data.
> 
> BELIEVE ME (and the other developers on the list should chime in), RE is the business of the 
> database engine, NOT the developer!
> 
> Creating the relationships in the relationship window is how you turn on RE.
> 
> 1) Open your relationship window.  Click, drag and drop from the PK in the parent to the FK in the 
> child.  An "edit relationships" dialog opens.  The top check box underneath says "Enforce 
> referential integrity.  Check that box.  THAT is what causes JET to perform all of the checks to 
> prevent ORPHANS.
> 
> 2) The next check says "cascade update...".  This has to do with cascading updates to the data in 
> the PK down into the child.  If you use autonumbers (surrogate keys) for PKs (and you should), then 
> the PK is never updated and you do not need to check this box.  If you use natural keys, then the PK 
> data can and will change, and those changes have to be rippled down into the child FKs.  Cascade 
> update causes JET to do that "cascade" update to the child FK fields.
> 
> 3) The last check box says "Cascade delete...".  This has to do with whether you want the database 
> to automatically delete child records if the parent record is deleted.  Like everything else, this 
> is fiercely debated but my opinion is that this is a BAD idea. The reason is simply that users tend 
> to ignore "are you sure" messages.  The more junior the user, the more they ignore the "are you 
> sure" messages.  Cascade delete on means that if the user accidentally deletes that client record, 
> the orders will just be automatically deleted.  The checks, invoices, communications, whatever else 
> you have child to client will be automatically deleted.  With one simple delete of one teeny little 
> record you can rip huge chunks of your database out.
> 
> It is a PITA but my method of handling this is to have a delete button which only specific groups 
> (supervisors / managers etc) can even see.  That button does the "are you sure" prompt, then runs 
> delete queries which delete the child records in the correct order back up to the parent.
> 
> Again, this is fiercely debated but how you handle it is less important than that you UNDERSTAND 
> what this stuff does and why you would or would not use it.  After that, it is on your head.
> 
> John W. Colby
> www.ColbyConsulting.com
>