Max Wanadoo
max.wanadoo at gmail.com
Wed Mar 10 09:18:37 CST 2010
Not really, when they click to delete the "header" record they get a msg
saying "xyz, AND ALL TRansaction Records", and if they still want to do it,
they get prompted for a password linked to their login details saying what
they can and cannot do. Only then do i delete it - and sometime,
occasionally, I wrap the delete code inside a BEGIN TRANSACTION END
TRANSACTION.
Max
On 10 March 2010 14:05, Rocky Smolin <rockysmolin at bchacc.com> wrote:
> Cascade delete has it's risks as you point out - but I find it very useful
> from the user's side for certain issues like deleting a purchase order or a
> sales order where, without cascade delete, the user has to delete each
> detail record from the P.O. or S.O. before they can delete the header
> record.
>
> Rocky
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, March 10, 2010 5:35 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] 2 Questions
>
> 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
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.733 / Virus Database: 270.14.129/2605 - Release Date: 03/08/10
> 23:33:00
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>