Referential Integrity

[YunqHero]

How does referential integrity prevent mistakes in a database?

[pkstormy]

Referential integrity enforces the relationships between tables and prevents orphaned records by 'structurally' enforcing that relationship between the 2 relationally joined tables. You cannot delete a record in a main table unless the relational record has first been deleted which keeps the data in synch. Orphaned records are one of the main reasons for inaccurate counts and mistakes in the data.

[YunqHero]

pkstormy, Thank you.

[pkstormy]

Your welcome. There is probably a more in-depth, politically correct explanation of this somewhere on the web. This is more of how I tend to think of it.

[pkstormy]

Referential integrity enforces the relationships between tables and prevents orphaned records by 'structurally' enforcing that relationship between the 2 relationally joined tables. You cannot delete a record in a main table unless the relational record has first been deleted which keeps the data in synch. Orphaned records are one of the main reasons for inaccurate counts and mistakes in the data.

As an added note: You can setup a relationship to cascade delete which will always automatically delete the record(s) in the relational table first and then delete the main record. Another thing Referential Integrity will check is when entering new records in the relational table it checks that the value entered in the relationally joined field has a matching value in the main table.

If you try to create relationships and there are orphaned or unmatched values in the relational records on that field, you'll get an error. If it does let you create this relationship with orphaned records or the integrity rules were broken (which I've rarely seen), I would follow these steps:

Easy method:
1. Delete and re-create the relationship. Check integrity by manually entering records into the tables.

More involved method:
1. first clean all orphaned records in all relational tables.
2. Consider creating a new db and then importing all the tables (without any relationships - ie. click the Options button)
3. Then create the relationships for all the relational tables. MSAccess 2010 does a good job on checking for bad data.

or to a higher degree:
Create a new db and all the tables along with the relationships. Then import all the data from the old tables into each table (you may have to save all the old autonumber values (that have relationships) into a new field in each table so it can be used to link the old data with the new data and grab the new autonumber value in your append queries - not for MSAccess beginners.)

When the referential integrity rules have been broken it's usually a sign of something going wrong structural with the database. I've only seen it broken about 1/2 dozen times in the last 25 years working with different data structures, usually in an older (MSAccess 97 mdb or with a corrupt mdb.) I would not trust that deleting and re-creating the relationship would resolve the issue. Typically there are signs of other problems when this happens.