Stuart McLachlan
stuart at lexacorp.com.pg
Wed Mar 10 16:01:51 CST 2010
I meant RI fo course,
--
Stuart
On 11 Mar 2010 at 7:53, Stuart McLachlan wrote:
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com