[AccessD] 2 Questions

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


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