[AccessD] SPAM-LOW: Re: 2 Questions

Max,

 > The reason I do not use it is because I got badly bitten by Cascading Deletes some years ago.

You are throwing the baby out with the bath water.  I use the RI capabilities of JET (or any other 
database) however I do NOT turn on cascade deletes for the reason you indicate.  "Enforce RI" and 
"Cascade delete" are two entirely separate check boxes for a reason.

It might be time for you to rethink your methodology.  To "do it manually" means to do it unreliably.

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
> Tony,
> 
> You are being mislead here. Nobody is saying that Referencial Integrity
> should not be practised. All I am saying is that I do not use the built in
> tools in Accesss to do it.  I do it manually.  Ignore the band wagon which
> has sprung up by those who didnt read it correctly (and if that statement
> doesn't get a response then I do not know what will !!).
> 
> The reason I do not use it is because I got badly bitten by Cascading
> Deletes some years ago. I now control what gets deleted and under what
> circumstances and not leave it to some "switch" being turned on in Access.
> 
> So, use Ref Int but you choose how to implment it.
> 
> Max
> 
> 
> 
> On 10 March 2010 13:34, jwcolby <jwcolby at colbyconsulting.com> 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 <http://www.colbyconsulting.com/>
>>
>>
>> Tony Septav wrote:
>>> Hey Asger
>>> I am not trying to start a debate. But if I do my joins in a query (with
>>> many many tables joined), if  I visually check my results  and find an
>>> error (or add code to test for errors), I can strip everything down  and
>>> rebuild it back up step by step.  This way I can verify my results and
>>> ensure referential integrity. If  1 does not equal 1 then I am in
>> trouble.
>>> Asger Blond wrote:
>>>
>>>> 2. I use Tools | Relationship to create foreign key constraints
>> ("enforce referential integrity"). Without foreign key constraints the data
>> will soon loose consistency.
>>>> Asger
>> --
>>  AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>