[AccessD] 2 Questions

Max Wanadoo max.wanadoo at gmail.com
Wed Mar 10 08:05:42 CST 2010


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
>



More information about the AccessD mailing list