[AccessD] 2 Questions

So how do you apply RE in Access without using the Relationships window?

And don't tell me that you use code behind every Delete, Insert, Update event.  - that is not 
using RE.  RE is *automatic* enforcement of referential rules at the database level.   

-- 
Stuart

On 10 Mar 2010 at 14:05, Max Wanadoo wrote:

> 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
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com