Link to home
Start Free TrialLog in
Avatar of brothertruffle880
brothertruffle880Flag 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?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

"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).

User generated image
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?


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.
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.