When NOT to Enforce Referential Integrity 3

[NickAtWork]

I have inherited a help desk database where some relationships enforce referential integrity, and some do not. One relationship is "Indeterminate" meaning that at neither end is there a primary key.

I see that in Access 97's Database Wizard creation "Asset Tracking.MDB" it also makes use of both relationships with and without enforced referential integrity.

I understand why ref. int. is good. I understand pros and cons of Cascade Updating and Deleting. (Neither of the above-mentioned MDB's uses Cascade Updating or Deleting.)

So, the question is, when is it a good idea NOT to enforce ref. int.? Or, when is it a bad idea to enforce it? Also, does an Indeterminate relationship have a use, or is always it an error in need of correction?

I cannot picture a scenario, but imagine I am missing something, since Enforcing is not the default for all relationships.

Thanks.

 

[JerryDennison]

It might be easier to define when you should enforce referential integrity and cascade update/delete.

Referential integrity should be enforced when the subdataset should NEVER contain records that do not have a corresponding master record. A good example is the order details of a sales order. You should not have any "orphaned" sales order details that don't have a corresponding master sales order record. By the same token you should set the cascade update/delete for this enforced relationships so that Access will automatically update or delete any of the related order details for a given sales order.

The opposite of this is records that are not directly dependent on another record for their existance. An example of this is lookup tables that provide for a modicum of consistency for naming purposes but do not limit the user to the list(s) provided.

I hope this makes at least a little sense to you.

 

[NickAtWork]

Thanks Jerry.

We're in agreement as far as you go. I guess another way to phrase my question is: "What is the difference between a relationship that does not enforce referential integrity and no relationship at all?" Or "What is the purpose of making a relationship if you are not going to enforce referential integrity?"

Thanks again.

Nick Pettijohn

 

[JerryDennison]

I guess one reason would be ease of use later in the database. When you define a relationship in the relationship view it gets carried automatically throughout the db. Saves a little work.

 

[tlbroadbent]

In my view, there are three levels of referential-integrity that can be established when defining relationships in MS Access.

Level 1:

Referential-integrity is not enforced. However, primary key/foreign key relationships are defined.

Level 2:

Define and enforce basic declarative referential-integrity constraints. The database engine prevents or restricts actions from being taken that would violate the integrity of the database.

Level 3:

Define actions or operations taken by the database engine to maintain the referential-integrity of the database. These are the cascaded actions such as deleting rows in foreign key tables when the corresponding primary key is deleted.

-----------------------------

I have seldom implemented level 3 in a production database. Perhaps part of the problem was the difficulty of setting it up properly. Mostly, it was and continues to be a matter of preference. I prefer to control the actions taken from within my application rather than allowing the database engine to take action behind the scenes. I believe that I lose control and visibility of what is happening when cascading actions occur.

I have implemented level 2 as defined above. This stops users from taking actions that violate the integrity of the data, such as deleting a primary key record or changing a primary key that has related foreign key records in another table.

Finally, I might implement level 1 relationships to help when creating queries, as noted by Jerry. I also find that establishing relationships without enforcing referential integrity is useful for documentation.

There have been circumstances when we have defined a relationship without enforcing integrity because the data already violated integrity rules. This usually happens when dealing with data from non-relational sources such as our legacy mainframe systems. What we attempt to do in these cases is clean up the data so referential-integrity can be enforced. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BNPMike]

The purist answer is that if there is referential integrity in the real world then you should represent it in the database.

I have to confess, like Terry, I tend not to use this feature but I'd have a job defending it in court.

To answer your question - Do not use it when you are developing or are the sole user. This way you have maximum flexibility. You do however have to be careful not to taint the database. Do use it when you roll the thing out. It is a free facility to protect against and highlight errors. Why refuse something that is free? If you really want to do everything yourself then why not use Java instead? That way you application can be riddled with errors and fall over all time just like all other Java applications.

Note referential integrity will adversely affect performance, but what's the point of a superfast application that contains invalid information? mike.stephens@bnpparibas.com

 

[NickAtWork]

Thanks for your responses.

I always use level 2 as a baseline when I am in control. I don't need orphan records any more than my users do!

I spoke to the creator of the database I inherited. He said he used the non-enforced relationships so that history would be maintained in the main table, even if source records are deleted. Personally, I think flagging the master record as inactive, filtering it, and never deleting anything is a better solution for maintaining history AND data integrity.

The question I am left with is what effect does a relationsip without referential integrity have versus no relationship. Will a query return a different result in one case versus the other? (I guess I could do some testing and figure it out, huh!)

Thanks again.

Nick

 

[tlbroadbent]

The relationship doesn't change the way the query functions. If you JOIN two tables via a column or columns the JOIN result will be the same whether rhe relationship has been defined or not. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JimHorton]

All,
If you define a relationship in Access without enforcing anything all it does is create the joins automatically when you create a query in the grid. A few saved mouseclicks,
and as noted, it also can help a developer new to the db to understand what's going on.

But in my opinion, you should always enforce the relationships, both during development and in production. If you enforce it only when rolling out, you most likely will run into surprises. Exceptions obviously exists, such as initial data load, etc, but you'll be more comfortable in the long run when you know the data's clean.
--Jim

 

[may1hem]

JimHorton: "If you define a relationship in Access without enforcing anything all it does is create the joins automatically when you create a query in the grid. A few saved mouseclicks, and as noted, it also can help a developer new to the db to understand what's going on."

Now I'm confused... I tried creating a relationship between 2 tables without enforcing ref integrity. Then I created a new query in Design View. I added the 2 tables to the query and, yes, it showed the non-ref-enforced relationship. So what? I don't see the benefit... Surely the tables either ought to be related, or not? If they are related, then a "proper" relationship should be created between them. If they are not related, then no relationship is needed between them. Any ideas?


tlbroadbent: "The relationship doesn't change the way the query functions."

Again, if these 'dud' relationships don't affect queries then what is the point of using such relationships?


tlbroadbent: "Finally, I might implement level 1 relationships to help when creating queries, as noted by Jerry. I also find that establishing relationships without enforcing referential integrity is useful for documentation."

How are these dud relationships useful for documentation?


NickAtWork: "I spoke to the creator of the database I inherited. He said he used the non-enforced relationships so that history would be maintained in the main table, even if source records are deleted."

How can these dud relationships help maintain history?


Thanks,

May

 

[JeremyNYC]

May,

No dud relationships. There are relationships without RI, and, as illustrated above, there are times when they are important (see the example of a list of states, above).

In every case you mentioned there is _some_ advantage to the relationship.

In the query you created, the relationship was already there for you after you added the second table. That's already been referred to as an advantage of a few mouseclicks.

It doesn't change the way the query works _as compared to one where you draw the relationship yourself_. A query with two tables and no relationship is an orthagonal query, and will have vastly different results to those of one with a relationship.

Relationships help document the database in that you can look at the relationship and see what tables are related to what other tables.

Nick wasn't saying that the relationships help maintain history, someone he works with said that. Nick had an excellent explanation of a much better way to maintain history.

This thread actually contains a lot of good information, if you read it carefully.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[may1hem]

I called them "dud" relationships because I don't see any real use for them.

Yes, I understand that there must be some advantage to using these relationships, but I just can't understand it from what has been written above. Perhaps someone could explain it another way?

Yes, it has been stated that these non-enforced relationships save a few mouse-clicks when it comes to queries but as I stated, this makes no sense to me. If the 2 tables need a relationship in a query, surely the 2 tables must be related and so they need a relationship with referential integrity defined in the main Relationships window?

tlbroadbent: "There have been circumstances when we have defined a relationship without enforcing integrity because the data already violated integrity rules. This usually happens when dealing with data from non-relational sources such as our legacy mainframe systems. What we attempt to do in these cases is clean up the data so referential-integrity can be enforced."

This explanation is the only one, so far, that makes sense to me. But even in this case, a non-integrity-enforced relationship is only a temporary solution.

JeremyNYC: "A query with two tables and no relationship is an orthagonal query, and will have vastly different results to those of one with a relationship."

Yes, I agree with this but as I said, what I understand is that either 2 tables are related & so need an integrity-enforced relationship, or they are not related and do not need a relationship at all.

"Nick wasn't saying that the relationships help maintain history, someone he works with said that. Nick had an excellent explanation of a much better way to maintain history."

I totally agree that Nick's own method was great. I wasn't accusing him of anything! I was asking if anyone knew why the creator of Nick's database said that relationships helped to maintain history. Personally, I can't see that. Was this person correct in what they said?

I have re-read this thread and see that everyone implicitly understands why 2 non-related tables might need a relationship when it comes to a query. Perhaps someone could enlighten me?

Thanks,

May

 

[TonyU]

[tt]Then again, those of us that program in ASP with either an Access or SQL database don't use this feature because we're not too concern about maitaining referenctial intregity due to the fact that most of us ( I hope) never delete but "Mark" or "Flag" for deletion.

<%=Tony%>