Free Lessons
Fast Tips
Topic Index
Home   Courses   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Back to Evil Access Stuff    Comments List
Referential Integrity Upload Images   Link   Email  
Jeffrey Rainey 
Can you explain Auto-numbers in relation to Referential Integrity and Cascading updates and deletes. You mentioned that you can't use Cascading with multi-user environment and/or tables in multiply files. So if you don't/can't use Cascading then how, or is it necessary, to keep Referential Integrity in all situations?  Thanks
Scott Axton
Whether in the single user db or in a multi-user db Auto Numbers are utilized to provide a unique id that you use to relate two tables and their records together only. You shouldn't depend on them as a piece of your data.  They don't get duplicated and don't get reused once they have been used and deleted.

In the case of multi-user environ it is up to you the developer / programmer to manage the relational integrity between records.  It is necessary to keep the RI for the sake of good clean data and to ensure valid records.

Other db systems such as SQL Server do not use the RI and Cascading Updates and Deletes
Why that is I can't tell you - only that it is and I / you just accept it and just deal with it.

I'm sure somewhere along the way Microsoft thought they were helping out inexperienced users but in reality it is one of those things that you need to learn the right way from the start.  Not grow to depend on a 'crutch' then have to re-learn when you get into more advanced topics.
Jeffrey Rainey
Thanks for the reply.

Quote: "Other db systems such as SQL Server do not use the RI and Cascading Updates and Deletes
Why that is I can't tell you - only that it is and I / you just accept it and just deal with it. "

Basically how is RI done in the SQL Server environment if a direct RI function is not available? Seems like that would be ideal to setup Access that way now so in the future the transition could be made easily.

Quote: "They don't get duplicated and don't get reused once they have been used and deleted."

I could have sworn that was one of the reasons to make a record inactive versus deleting, to avoid reusing an auto-number.
Scott Axton

Basically how is RI done... ?  
As I mentioned - programmatically by you the developer.  It is up to me to maintain the RI.
That is why Richard says don't rely on it.  He teaches early on not to depend on Accesses in built functionality because that way you don't have to make a transition.  You are already working with the tables correctly.

True on marking inactive.  BUT due to space, speed, etc. you may want or even need to delete records.
For instance an Order and the Order Detail records.  If the customer of that order moves or dies and you delete the customer
it would 'break' the referential integrity of the data.  That would alter sales data needed for taxes for instance.

Instead you might move the records to a history table then delete the records from the current table.  You wouldn't want to reuse deleted auto numbers because of existing data else where.

Scott Axton
For me I just don't concern myself too much on the why is is that way.  I understand the concept, accept it, and work with it.  There are too many other things for me to learn and do and deal with to get the job at hand done.

My father used to tell me many many moons ago, "Don't sweat the small stuff".  
Richard Rost
First off, Cascade UPDATES are dumb. That's basically saying if I change a Primary Key in one table, update all of the Foreign Keys in related tables to match... but we're using AutoNumbers for PKs, so that will never happen in any database you build in my classes. If you were using something dumb, like a Phone Number for someone's PK, then sure, you might want a Cascade Update if someone changes their phone number. But with AutoNumbers, don't worry about it.

Cascade DELETES are just dangerous. Be very careful with them. If you set up Cascade Delete between, say, Customers and Orders, and then someone accidentally deletes a customer, then BYE BYE there go all your orders for that customer and then your books are all screwed up. So I just don't like them for MOST situations. Sure, they're useful in a few temp-table situations, but... Meh.
Richard Rost
I prefer to handle something that involves Cascade Deletes MYSELF with a little code. Want to delete an order? OK... ask the user if they're sure, use a little SQL to delete all the order details, then delete the order. A few lines of code. Everyone is happy. Of course, you shouldn't delete an order anyhow (mark it canceled).
Richard Rost
Now as far as general Referential Integrity goes, the major benefit is that you can use it to prevent Orphans. For example, you can't add an order detail item without a parent order. You can't add an order without a customer. Those kinds of things are fine. But cascade UPDATES are dumb, and DELETES are dangerous and unnecessary.
Richard Rost
You CAN use referential integrity in a multi-user or split database, however you cannot enforce RI between multiple database FILES. So as long as OrderT and OrderDetailT are in the same ACCDB file, you CAN enforce RI between them. If they're split into separate files, you cannot. Again, you'll have to handle that in your VBA/SQL code manually. Not super hard to do.
Richard Rost
And yes, of course SQL Server does support RI, Cascade Updates and Deletes... but I don't use them there for the same reason I don't use them in Access. Dumb and dangerous. :)
Richard Rost
Learn more in my Relationships video (RI in the extended cut) and in the Relationships Seminar.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Evil Access Stuff.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/27/2024 5:46:17 AM. PLT: 1s