Enforce Referential Integrity

[Acceesbility]

Hello

It seems that when creating table relationships one always selects;
"Enforce Referential Integrity"

Why doesn't Access just do it automatically when creating a relationship.
The lesser used option might then be "Disable Referential Integrity" when it's not needed.

That said, when would one choose not to "Enforce Referential Integrity" ?

 

[sneuberg]

I believe with only the relationships defined, Access will add the joins between the tables when you create queries. So for those who want to do their own integrity enforcement they would have this benefit. That's my guess.

As an aside we check "Enforce Referential Integrity" in our applications but we use this only as a backup to the code we write to enforce reference integrity. The reason why we write our own code is because the error Access raises when there is a violation really sucks. It doesn't provide any information as to what tables and fields were involved so we can't make a user friendly error message out of it.

 

[Acceesbility]

Hello sneuberg
Thanks

It never entered my mind that you'd code the Enforcing.
Would you have an example of what the code looks like that might be used for Enforcing Referential Integrity?

 

[sneuberg]

Here's some code which covers deletes. There's a DCount for each relationship Brand has with other tables. Pain in the ass. Actually this is poorly written as we don't give the user any more information than they would have received had we just trapped the error.

If DCount("[Brand]", "[Item Master TBL]", "[Brand] = '" & SQL_Current_Types_List & "'") _
    Or DCount("[Brand]", "[Brand Numbers Link TBL]", "[Brand] = '" & SQL_Current_Types_List & "'") _
    Or DCount("[Brand]", "[Prepack TBL]", "[Brand] = '" & SQL_Current_Types_List & "'") _
    Or DCount("[Brand]", "[Components TBL]", "[Brand] = '" & SQL_Current_Types_List & "'") _
    Or DCount("[Brand]", "[Subassembly TBL]", "[Brand] = '" & SQL_Current_Types_List & "'") > 0 Then
    MsgBox "You cannot delete this brand because it is used in the database.", vbExclamation, "Cannot Delete"
    Me.Brand.SetFocus
    Me.Current_Types_List.Value = Null
    Exit Sub
End If

Edit: Actually this isn't right and we need to fix this. There should be a > 0 after each DCount rather than just one at the end, albeit this works correctly as when DCount returns 0 it acts as being a False.

 

[The_Doc_Man]

While I can't show you the code because I've retired and don't own the project, I can tell you that we used to apply the positive side of Murphy's Law when we coded so that RI issues never bit us on the butt.

If you weren't sure, let me explain: Ed Murphy was a rocket scientist. (Yes, Murphy's Law really IS rocket science.) He worked on the human-factor testing using rocket sleds to see how much acceleration the human body could tolerate. One day, some poor test subject pulled 9 G's on a sled only to find that the plug for the instruments was upside down - so no data was actually gathered. This led to Murphy's Law, but most people only recall the first half of the FULL law...

"What can go wrong WILL go wrong..." (and the 2nd half) "so fix things so that they cannot go wrong." This is why your computer hardware plugs in using asymmetrical plugs with a bar or notch on one side but not the other. It is why power plugs are polarized by having a big prong and a little prong side by side - to prevent phase issues for an object with multiple power sources.

Where this applies here is that the code you write and the forms you build must not OFFER the option to build a child record until the parent record is already known to exist. Don't identify parents using a text box; use a combo box instead. If you offer options to build child records, only list the extant parents in the combo box (or however you actually implement it.) Then RI never causes a trap.

 

[stopher]

Hello

It seems that when creating table relationships one always selects;
"Enforce Referential Integrity"

Why doesn't Access just do it automatically when creating a relationship.
The lesser used option might then be "Disable Referential Integrity" when it's not needed.

That said, when would one choose not to "Enforce Referential Integrity" ?

Selecting "Enforce Referential Integrity" is enforcing mandatory participation i.e. if I put a vlaue in this table then it MUST exist in the related table.
But you may want optional participation. Consider a car booking system that books from cars in their own fleet but also occasionally books cars not in the fleet. So the booking table has a field called CarReg. The car fleet table only has vehicles in the fleet. We want to relate the booking table to car fleet but also allow car registrations to be entered that are not in the fleet. So we could use the option to not enforce referential integrity.

Also, note that models do not have to be "relational" in the Codd sense. You might be pulling data from various sources to do some number crunching and you might find that creating the relations in the relationships view helps your view of the model.