Others

How to Enforce Referential Integrity in Access One-to-One Relationships

Natalie TaylorNatalie Taylor Nov 4, 2026 869 views

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.

How to Enforce Referential Integrity in Access One-to-One Relationships
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.
Before you start

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.

Solution 1Recommended

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.

1
Set unique indexes

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.

2
Open the Relationships window

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.

3
Link the tables

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

4
Enforce Referential Integrity

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.

Configure the Directional One-to-One Relationship
Relationship Direction Verified: Once saved, Access will draw a line with '1' on both ends, indicating a proper one-to-one relationship with strict referencing rules applied.
Free Microsoft Office alternative

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. 1. Download the installer: Visit the official WPS Office website and download the free installation package.
  2. 2. Install the software: Run the installer and follow the on-screen prompts to set up WPS Office on your computer.
  3. 3. Open your files: Launch WPS Office and directly open your existing Microsoft Word, Excel, or PowerPoint files without losing any formatting.
Fully compatible with Microsoft Office formats (.docx, .xlsx, .pptx).Lightweight design that runs smoothly on almost any device and OS.Includes a robust Spreadsheet application for advanced data filtering, pivot tables, and analysis without complex database setups.Free built-in PDF tools for easy document conversion, merging, and editing.
microsoft office alternative - wps office

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.