[AccessD] Cascade-delete (was: Estimating Help)

Sure, if the user (any user who happens on the data BTW) is allowed to
delete some given data, then the cascade delete can be safely turned on for
child records of that data.

If the "Southeast Sales Executive" down the hall is NOT allowed to delete
the data then you are back to using cascade delete to prevent him from doing
so, and building queries to ALLOW the authorized users to do the deletes.

The point is that by turning on cascade delete in any given instance you are
allowing ANYONE in the world to delete that data.  Only you know whether
that is appropriate.  If it is, then fine, turn it on.  By turning it OFF,
you are forcing the use of query sequences to delete records if children
exist.  This in no way guarantees that things won't go wrong.  The user can
delete the children, then the parents (manually).  But you have added a very
powerful tool to the job of protecting your data from accidental deletion.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Sunday, February 02, 2003 7:36 AM
To: John W. Colby
Subject: Re: [AccessD] Cascade-delete (was: Estimating Help)


Hi John

>>I guess your intention is to warn the less experienced developer from
>>applying RI with cascade-delete set to on. This warning is right -

> Correct, that is my intention.  Actually the point is that RI is set up by
> default with cascade delete off.  LEAVE IT (cascade delete) OFF!  If you
> need to delete things, do it with queries and transactions.

> I will admit to turning cascade delete on as an administrator, deleting
all
> the data for a specific massive set of records in 20 related tables, then
> turning cascade delete back off.  I could have done it with queries, but I
> am the DBA and I know what I am doing.

> Using cascade delete as a tool to allow users to delete records is risky.
> You may be able to find specific instances where it works without any
risks,
> but as a rule - "ya shouldn't be doin' that".

OK, I think we are narrowing this in now to my initial point - as a
comment to Arthur who seems to abandon cascade-delete totally.

I know you love your Trash flag to prevent to physically delete
records; other use timestamps but the principle is the same. And those
records may have valuable child records like order statistics etc.
With the lower price on disk space and faster servers and workstations
it has become easier to stick to this non-delete principle.
In those cases cascade-delete is turned off for those tables.

What I am talking about is strictly child records of no value without
their parent record. We are now talking about clean-up which is
another situation than erasing information. I can think of examples
like child tables with properties useless without the parent record,
linking tables (some only hold pairs of keys from two tables), and log
tables with various information in child tables.
In those cases cascade-delete can safely be turned on for those
tables.

/gustav

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com