Others

Fix Referential Integrity Errors in Microsoft Access Relationships

John WilsonJohn Wilson Nov 14, 2026 869 views

Question details

Enable referential integrity for a relationship between parent and child tables in Microsoft Access.

Fix Referential Integrity Errors in Microsoft Access Relationships
Product
Microsoft Access
Device & OS
not provided
Scenario
The user wants to create a relationship that requires child records to reference valid parent records.
Observed behavior
Access cannot enforce referential integrity because some child-table records have no matching parent record.
Before you start

Identify the parent table's key and the corresponding foreign key in the child table. Make sure the parent key is a primary key or has a unique index, and that the related fields have compatible data types and sizes.

Solution 1Recommended

Find and resolve orphaned records before enforcing the relationship

Use an unmatched query to identify child records without a parent, correct the data, and create the relationship again.

1
Create an unmatched query

In Access, select Create > Query Wizard > Find Unmatched Query Wizard. Choose BitlyItems as the table containing the records you want to find and Bitly as the related table.

2
Match the key fields

In the wizard, select the foreign key in BitlyItems and the corresponding key in Bitly. Choose the fields you need to identify the child records, then finish and run the query.

3
Resolve the unmatched records

Review the query results and correct invalid foreign-key values in BitlyItems, add the missing parent records to Bitly, or delete child records that are no longer needed. A blank foreign key may be valid if the relationship is optional.

4
Enable referential integrity

Select Database Tools > Relationships. Add both tables if necessary, drag the parent key from Bitly to the corresponding field in BitlyItems, select Enforce Referential Integrity, and click Create. For an existing relationship, double-click its line and enable the option in Edit Relationships.

Find and resolve orphaned records before enforcing the relationship
If Access still cannot enforce the relationship: Check the related field types and sizes and verify that the parent key is unique. For example, an AutoNumber parent key typically matches a Number field with the Long Integer field size.
Free Microsoft Office alternative

Review exported Access data with WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Office format compatibility. Export Access tables to Excel to review their data in WPS Spreadsheets for an easy transition between tools. WPS does not enforce or repair Access database relationships.

Review exported parent and child table data in spreadsheetsCompatible with Microsoft Excel XLSX and XLS filesFamiliar spreadsheet interface for an easy transitionFree and lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

What is an orphaned record in Access?

It is a child-table record whose foreign-key value does not match a key in the parent table. Use Create > Query Wizard > Find Unmatched Query Wizard to locate such records.

Do I have to delete every unmatched child record?

No. You can correct the child record's foreign key or add the missing parent record instead. Delete a child record only if it is no longer needed.

Can a child record have a blank foreign key?

Yes, if the foreign-key field permits Null and the relationship is optional. A non-Null foreign-key value must match a parent key when referential integrity is enforced.

Why does Access still reject referential integrity after I fix the records?

Check that the parent field is a primary key or has a unique index and that the related fields have compatible data types and sizes. Then open Database Tools > Relationships and try enabling Enforce Referential Integrity again.