jwcolby
jwcolby at colbyconsulting.com
Thu Mar 11 07:24:14 CST 2010
> Even JC talks to God. What is worse, God apparently answers. Probably because God got the JC's
mixed up with the same initials 'an all (Should have used RI, eh?)
LOL, while I do talk to God and he does answer (not a bad thing!), you may believe he never mixes me
up with the other JC. ;)
John W. Colby
www.ColbyConsulting.com
Max Wanadoo wrote:
> Ok, ok, ok, enough already.
>
> I'm too old. I'm too tired.
>
> Even JC talks to God. What is worse, God apparently answers.
> Probably because God got the JC's mixed up with the same initials 'an all
> (Should have used RI, eh?)
>
> Now can I get back to work?
>
> Tony - hope you have learned sommat from all this! Recite the Mantra.
> Maintain the Dogma.
>
> Max
> Ps. Actually, I do use RI but I was getting bored.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
> Sent: Wednesday, March 10, 2010 11:00 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] 2 Questions
>
> Max,
> I was just about to ask the same question when Stuarts posting arrived.
> In a previous response you wrote:
>
>> 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.
>
> So: what exactly do you mean by "manually"?
> Essentially constraints can be enforced in two ways: *declarative* and
> *procedural*.
> A *declarative* foreign key constraint means that the constraint is part of
> the table definition itself. This is what you get when using the
> Relationship-Tool. You can of course set up this constraint by hand writing
> a SQL like this:
> ALTER TABLE tblOrder ADD CONSTRAINT FK_Order_Customer FOREIGN KEY
> (CustomerID) REFERENCES tblCustomer (CustomerID).
> Is this what you mean by establishing RI "manually"? If so: WHAT A WASTE OF
> TIME ... since you can accomplish exactly the same with a few mouse clicks
> using the Relationship-Tool.
> A *procedural* foreign key constraint means that the constraint is not
> enforced in the table definition, but by some code "behind every Delete,
> Insert, Update event" (to quote Stuart). Is this what "manually" mean? If
> so: WHAT A HUGE WASTE OF TIME ... and WHAT A HUGE EXPOSURE TO CONSTRAINT
> VIOLATIONS if users can get access directly to the tables or if you forget
> to implement the code in some of your forms.
> Actually it could be argued that a *procedural* foreign key constraint isn't
> a RI constraint at all. It might nominate as a conceptual constraint, but
> not as a bulletproof physical constraint - and isn't this what we all expect
> a RI to be?
>
> Asger
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
> Sendt: 10. marts 2010 22:54
> Til: Access Developers discussion and problem solving
> Emne: Re: [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
>
>