[AccessD] 2 Questions

 > As for how "dangerous" cascade-deletes are, it is not different from any other action that alters 
or deletes data in the database.

Well... I disagree of course.  The problem is that cascade deletes deletes EVERYTHING down to the 
last great-great-great-great grandchild record (assuming Cascade delete everywhere).  That is pretty 
darned dangerous.

I have a database with

Insurer
Policies
Policy Holders
Claims
Claim events
Benefits
Benefit payments
Checks

Etc.  There are probably 30 or 40 tables "child to" (descendant of) Insurer.

One delete of an insurer could delete literally TENS OF THOUSANDS of child / grandchild etc records. 
  In fact it WOULD LITERALLY wipe out an entire line of business!

That is NOT something any average user should be able to do.  In fact I would argue that this is 
something that only a manager, or even the DBA himself should be allowed to do.  The DBA would be 
able to make sure that the manager understood the consequences of the action.  Then if it is 
supposed to happen fine.  A sequence of queries deletes from the bottom up.

After backup of course.  Which brings up the other big problem with cascade delete.  With Cascade 
Delete how do you ensure that everything being deleted is recoverable when the user goes "OH SH*T"!

IMHO cascade delete should be used sparingly if at all.

Which is entirely different from Referential Integrity.

John W. Colby
www.ColbyConsulting.com


Gustav Brock wrote:
> Hi Max
> 
> It is not "some" switch. There is one specific switch that controls cascade-deletes for a relation. 
> 
> If anyone is unsure how to operate this or to handle referential integrity in general, I will certainly recommend to read it up at the soonest as this is the heart and soul of a relational database.
> 
> As for how "dangerous" cascade-deletes are, it is not different from any other action that alters or deletes data in the database.
> 
> /gustav
> 
> 
>>>> max.wanadoo at gmail.com 10-03-2010 15:05 >>>
> 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.
> 
> 
>