jwcolby
jwcolby at colbyconsulting.com
Wed Mar 10 08:56:50 CST 2010
Rocky,
I understand your point. My point is simply that the average user has no business deleting records
in the database. In general this should be a supervisor (or higher) decision. A button which runs
a series of delete queries does the same thing as "individually deleting the child records".
Whether you use a button simply forces the user to think a little more about the delete. If it is
hidden unless you are a supervisor completely removes the issue from the average user. How many
times have you hit the delete key by accident, then you are not looking at the keyboard and hit
enter. The "ARE YOU SURE" message may have just been answered affirmatively. Either way, I have
seen too many users just click "YES", muttering under their breath "of course I'm sure" and they
haven't even read what it is they are saying yes to.
If you have specific instances where cascade deletes are useful then fine. Making that the DEFAULT
is bad practice (IMHO).
John W. Colby
www.ColbyConsulting.com
Rocky Smolin 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
>
>
> 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
>