Avatar of brothertruffle880
Flag for United States of America asked on

Access 2010 - Referential Integrity - Orphan Records

I'm trying to understand when a database has referential integrity.  Can someone give me a simple example of this and also what an orphan record is?
Microsoft Access

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Dettman (EE MVE)

Just to add a tad, "orphaned" stems from the fact that one to many relationships are also refered to as Parent/Child relationships.   If you delete the parent and leave the children behind, they are orphaned.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"I'm trying to understand when a database has referential integrity"
At database per se does not have Referential Integrity (RI). 'Related' Tables within a database may or may not have RI enabled.

Specifically how you can tell is by opening the Relationships Window. Then Right Click and select Show All. If you see nothing but MSYS tables, then you have no Relationships in the database.  

IF ... you see table - with connecting lines between them (see image), then double click on the line, and the Edit Relationships dialog will open.  Look to see if Enforce Referential Integrity is checked.  IF so, this tells you that RI is established between those two tables (again, see image).

Jeffrey Coachman

One more...

Here is the low level explanation I use when teaching:

Referential Integrity=Data Protection
It protects against two things:
1. Accidental Additions
2. Accidental Deletions

1. Accidental Additions
You cannot add a "Many/Child" record (ex.: and Order for a Customer) unless the One/Parent Record exists first.
(The Customer must exist first, before you can add an order for them)

2. Accidental Deletions
You cannot deletes a One/Parent Record, if a Many/Child record exists.
In order to delete a Customer, you must first delete all the Orders associated with them.

Orphaned Records:
When a One/Parent record is deleted leaving just the Child/many record(s).
Ex.: You delete a Customer record, but that customer still has Order records.
...So what is the problem with this? (you say, ..."Well at least I still have the Orders")
Because the Order table will only contain the CustomerID, ...this means that you will not have any of the other Customer info:
Name, Address, Phone, Email, Contact Name, ...etc...

...So how are you going to bill them for the unpaid orders?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Hamed Nasr


From real life.

a line of parents.(line1) each parent has a name tag.
a line of children (line2)  each holds two tags one for hisNameTag and other for hisFatherNameTag.

Referential integrity requires each child in the line should have a parent in the parents' line.

If he has no parent, he is an orphan

Now to programming, line1 is table Parents, and line2 is table: Children they have fields

Parents(parentName, ...)
Children(childName, parentName, ...)
The two tables are related through parentName.

When not to use referential integrity?
During data preparation although not preferred. During data building and if  there is no parent for a child, add a virtual parent and modify later. You may add one virtual parent for all children with a missing parent This of course has to be modified with the real parent values.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

No big deal, but I just wanted to point out that re " understand when a database has referential integrity" ... the easiest way to tell 'when' ... is looking that the Relationships window as I posted in the screen shots.