Max Wanadoo
max.wanadoo at gmail.com
Wed Mar 10 09:15:57 CST 2010
> To "do it manually" means to do it unreliably.
Moi! I think you are thinking of some other programmer ;o)
Max
On 10 March 2010 14:50, jwcolby <jwcolby at colbyconsulting.com> wrote:
> Max,
>
> > The reason I do not use it is because I got badly bitten by Cascading
> Deletes some years ago.
>
> You are throwing the baby out with the bath water. I use the RI
> capabilities of JET (or any other
> database) however I do NOT turn on cascade deletes for the reason you
> indicate. "Enforce RI" and
> "Cascade delete" are two entirely separate check boxes for a reason.
>
> It might be time for you to rethink your methodology. To "do it manually"
> means to do it unreliably.
>
> John W. Colby
> www.ColbyConsulting.com <http://www.colbyconsulting.com/>
>
>
> 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/> <
> 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
>