How to Enforce Referential Integrity in Access One-to-One Relationships
Question details
The user needs to enforce a directional one-to-one relationship in a database where a child record requires a parent record, but deleting the child does not delete the parent.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a relational database with strict deletion rules for parent and child tables to preserve independent parent records.
- Observed behavior
- The database must allow independent parent records (e.g., addresses) but restrict child records (e.g., cleaning records) from existing without a parent, while preventing cascaded deletions from the child back to the parent.
Ensure you have a clear understanding of which table acts as the primary (referenced) and which acts as the related (referencing) table. Close any open forms or queries that might lock the tables before modifying relationships.
Configure the Directional One-to-One Relationship
Define the parent table as referenced and the child table as referencing, enabling referential integrity while keeping cascade deletes disabled.
A one-to-one relationship is directional. By correctly establishing the primary key and foreign key index properties, you dictate which table controls the other. Ensuring referential integrity guarantees no orphaned records are created.
Open your referencing (child) table in Design View. Select the foreign key field (e.g., AddressID) and set its 'Indexed' property to 'Yes (No Duplicates)'. This enforces the strict one-to-one rule.
Navigate to the Database Tools tab on the ribbon and click on 'Relationships'. Add both the referenced (parent) and referencing (child) tables to the workspace.
Click and drag the primary key from the referenced table (e.g., tblAddresses) and drop it onto the corresponding foreign key in the referencing table (e.g., tblCleaning).
In the Edit Relationships dialog box that appears, check the box for 'Enforce Referential Integrity'. Ensure that 'Cascade Delete Related Records' remains unchecked so deleting a cleaning record does not delete the address.

Manage Record Deletions via Access Forms
Use structured forms or controlled buttons to ensure users delete only the child record without mistakenly affecting the parent data.
Need a Lightweight Alternative for Your Daily Office Tasks?
While Microsoft Access handles highly complex database management, everyday documentation, data analysis, and presentations can be seamlessly handled by WPS Office. It provides a free, highly compatible, and user-friendly alternative to Word, Excel, and PowerPoint.
- 1. Download the installer: Visit the official WPS Office website and download the free installation package.
- 2. Install the software: Run the installer and follow the on-screen prompts to set up WPS Office on your computer.
- 3. Open your files: Launch WPS Office and directly open your existing Microsoft Word, Excel, or PowerPoint files without losing any formatting.

Frequently Asked Questions
What happens if I enable Cascade Delete in an Access relationship?
Enabling 'Cascade Delete Related Records' means that when you delete a record in the primary (referenced) table, Access automatically deletes all related records in the referencing (child) tables to maintain referential integrity. It does not work in reverse (deleting a child will not delete the parent).
How do I ensure my relationship is one-to-one instead of one-to-many?
To enforce a one-to-one relationship in Access, the linked field in both tables must be set as the Primary Key, or the foreign key in the referencing table must have its 'Indexed' property set to 'Yes (No Duplicates)'. Otherwise, Access will default to a one-to-many relationship.
Why am I getting an error when trying to enforce referential integrity?
This error usually occurs if the tables already contain mismatched data (orphaned child records that have no matching parent record) or if the related fields have different data types. You must clean up existing data and ensure data types match perfectly before Access allows you to enforce the rule.
Can I temporarily disable referential integrity to mass-import data?
Yes. You can edit the relationship and uncheck 'Enforce Referential Integrity', perform your bulk imports or data cleanups, and then re-enable the enforcement. Just ensure the final data perfectly matches the required relationship rules before turning it back on.




