Others

Fix Microsoft Access Referential Integrity Cannot Be Enforced

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 869 views

Question details

The user needs to enforce referential integrity in Microsoft Access but is blocked by unmatched foreign-key values in the child table.

Fix Microsoft Access Referential Integrity Cannot Be Enforced
Product
Microsoft Access
Device & OS
not provided
Scenario
The user is trying to create a relationship between a parent table and a child table, such as Customers and Orders, and enforce referential integrity.
Observed behavior
Access prevents the relationship and shows a referential integrity error because the child table contains orphan foreign-key values, including CustomerId equal to zero.
Before you start

Open the database in Microsoft Access and make sure it is in a trusted location so relationship and design changes are allowed. Identify the parent table, child table, and the exact fields used in the relationship before editing records.

Solution 1Recommended

Locate and Correct Unmatched Foreign-Key Values in the Child Table

Use this solution when Access refuses referential integrity because the child table contains orphan records or foreign-key values such as 0 that do not exist in the parent table.

Access enforces referential integrity only when every value in the foreign-key field matches a primary key in the parent table. Orphan values such as 0, blank, or IDs that no longer exist block the relationship.

1
Open the child table in Datasheet View

In the Microsoft Access Navigation Pane, double-click the child table, for example Orders, to open it in Datasheet View.

2
Inspect the foreign-key field

Locate the column that stores the parent key, such as CustomerId, and check for values that do not appear in the parent table's primary key field.

3
Correct or delete orphan records

For each unmatched value, type a valid CustomerId from the Customers table, or delete the record if it is no longer needed.

4
Close and save the table

Close the child table, reopen the relationship editor, and try enforcing referential integrity again.

Locate and Correct Unmatched Foreign-Key Values in the Child Table
Referential integrity can now be enforced: Once all child records point to existing parent keys, Access should allow the relationship to be created with referential integrity enabled.
Free Microsoft Office alternative

Use WPS Office as a Free Microsoft Office Alternative for Daily Document Work

Microsoft Access remains the correct tool for .accdb and .mdb database relationships, but WPS Office is a free, lightweight alternative for the daily Word, Excel, and PowerPoint work around that database. It supports Microsoft Office formats, offers a familiar ribbon interface, and helps you migrate documents without losing formatting.

Free companion Office suite for Microsoft Access users who still need to create reports, letters, and spreadsheets around database workOpens and saves Microsoft Word, Excel, and PowerPoint formats with high compatibilityLightweight and easy-to-use ribbon interface that feels familiar from Microsoft OfficeNo subscription required for core document editing and PDF tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does Access say referential integrity cannot be enforced?

Access prevents referential integrity when the child table contains foreign-key values that do not match any primary key in the parent table, such as CustomerId equal to 0. Open the child table and correct or delete those unmatched records, then try again.

How can I quickly find unmatched CustomerId values in Orders?

In Microsoft Access, create a query in Design View, add both the Orders child table and Customers parent table, join CustomerId to the parent primary key, and set the parent key criteria to Is Null. This returns the orphan child records.

Should I clear the Default Value on a foreign-key field?

Yes. Open the child table in Design View, select the foreign-key field such as CustomerId or ProductId, and delete the Default Value property. This prevents new records from automatically being assigned zero.

Can WPS Office open an Access database to fix this issue?

No. WPS Office does not edit .accdb or .mdb database files. Use Microsoft Access for database relationship tasks, and use WPS Office as a free alternative for Word, Excel, PowerPoint, and PDF document work.