relationships needed?

Posted on 2013-09-24
Medium Priority
Last Modified: 2013-09-25
Dear experts -
Sorry for the provocative title!
I was having major issues with cascading deletes in a form, so I made some changes to the recordset as well as removed ALL relational integrity enforcement/cascading deletes in the Relationships setup for the back-end.
The problem is gone.
However, I'm wondering now about the macro questions - do I need the Relational Integrity checking? Can you have a good relational database in Access just using the native forms/subforms without it?
Can you please lay out the arguments pro/con?
I know it's both a basic and a big question.
Thanks so much -
Question by:terpsichore

Expert Comment

ID: 39520268
Can you have a good relational database in Access just using the native forms/subforms without it?
No, you can't have a good relational DB without enforcing the constraints.
You might have a satisfactory relational db, but if your database complexity will increase in time, you will end up with a total mess, aka orphaned rows, partially missing data etc..
LVL 86

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39520559
So you removed the relationships, or you removed the Cascade Delete/Update enforcement on the relationship?

IMO Cascade Update and Delete are useless in a properly designed database.

That said: You must enforce constraints as some level. Whether that level is at database engine or at code level is up to you. I've built plenty of simple databases with no referential integrity at the engine level, but it was definitely more work to insure I had valid data in my foreign key fields. All in all, I think you're probably better off enforcing referential integrity at the engine level, but NOT enforcing Cascade Updates or Deletes.

Author Closing Comment

ID: 39520563
Thanks - this was the level of detail I was looking for. I reinstated just the referential integrity. (Indeed, we are enforcing the relationships - the structure is properly normalized - through code, e.g., through the subform key-field linkage, which has served us well to date.)

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question