Unless you need to use referential integrity or the cascade delete stuff for some reason, theres no need to use that.

Adam,

Thanks for your reply...

So the only time to set an Explicit Relationship would be when you want Referential Integrity in order to Cascade Update or Delete Records then, is that correct?

Further, do I understand correctly that When setting Referential Integrity, Cascade Update is automatic and I don't have to necessarily check that box?

I almost never use the relationship window and just handle the relationships outside of it. Referential Integrity, Cascade Update, and Cascade Delete are 3 separate things. I don't believe cascade update is automatic, i think you need to check it. Referential integrity is for preventing orphans mostly (this is what says you cant delete this record a related record is in another table, and you have to delete that one first).

You need referential integrity to prevent accidental deletions for the most important tables in your database.  If you hit the Delete key by accident while selecting a row in a parent table, referential integrity will prevent the deletion.  This is a "prevent you from yourself" kind of thing.  If you are the admin and you have all access to everything, then you are the one who needs the most protection, not your users.  A deletion like that could be big trouble.  If the deleted ID field is autonumber, you can't bring that back because autonumbers don't repeat.

Restore Deleted AutoNumber

Adam,

Thanks for your response.

Kevin,

Thanks for reaffirming what Referential Integrity does!

Gents,

The initial question I have is... when do I NEED to set Explicit Relationships between the tables in the Relationships Window.

In the attached pic... The CraftLevelsT appears to me to be nothing more than Lookup Table for the CraftLevel field in the other tables and NOT in need of an Explicit Relationship being set.

Is that about right?

Related: Relationships

Gents,

Here is a screenshot from Expert Level 7 dbase... which shows that Rich is not setting Explicit Relationships between the IDs of all tables.

Is there a general rule of some sort that should determine WHEN Explicit Relationships should be set?

(I've moved this question here, no need to create a new one asking the same thing again.)

Does the above not already answer it?

Well... I don't necessarily see a definitive answer to the question.

Adam responded with "Unless you need to use referential integrity or the cascade delete stuff for some reason, theres no need to use that." and... "I almost never use the relationship window and just handle the relationships outside of it."

And yet, I believe it was Sami who, in another post, stated that Explicit Relationships need to be set between ALL tables. So, can you see my confusion in the ambiguity between the replies?

I'm simply looking for some sort of definitive answer or logic to when to set an Explicit Relationship because it doesn't appear to be necessary to set all of them.

Further, in Expert Level 2.02 Video, Rich stated that Ad Hoc Relationships are "...perfectly fine for 90% of your relationships."

So, the answer I suppose then is to set an Explicit Relatioship only when I need to use Cascade Delete.

Is this correct?

Yes. You do not need to use them at all.

The only benefit (and con depending on what you're trying to do) is that you can't orphan records in the sub-tables. You can control as much or as little of that yourself in VBA if you want to. Or just remove the ability to delete all together. There's a million ways to handle the relationships outside of that relationship window. So unless you specifically need one of the 3 features it offers, and you don't want to mimic that in VBA, then you would use it. Otherwise there is no point.

It really depends on what you are trying to do. If you have users that are delete happy and will orphan all your records, then you would want to either use it, or do something else like turn off allow deletions or use VBA to control that. If you need to be able to delete records frequently for some reason and the orphan isn't an issue, then you don't need to do anything. There's not really an always answer. I just prefer to control it outside of the relationships if I need it.

As Adam said:

Or just remove the ability to delete all together.

I almost always turn off deletions and create my own Delete button (for non-critical data) so I have FULL control over the entire process. It does pretty much depend on the database and the tables involved.

Thank you all for your replies!

This helps immensely!