How to Fix Microsoft Access Cannot Create Relationship Error
Question details
The user is unable to create relationships or enforce referential integrity between tables in Microsoft Access due to non-normalized table designs.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a relational database and attempting to link an Order table acting as a bridge to customer and product tables.
- Observed behavior
- Access prevents the creation of a relationship and the enforcement of referential integrity because multiple comma-separated values are stored in a single record, violating database design principles.
Before modifying your table relationships, close all active forms and queries that depend on the existing tables, and review your current data to identify fields containing comma-separated or multiple values.
Redesign and Normalize Your Database Tables
Restructure your database to eliminate multi-value fields by creating dedicated tables for orders and order details, allowing Access to enforce referential integrity.
Relational databases require each field to hold a single, atomic value. Storing multiple items in a single record violates these principles and prevents Access from creating valid relationships.
To fix this, you must break down the unnormalized 'bridge' table into a main Orders table and a related OrderDetails table.
Navigate to the Create tab and click Table Design. Create a new 'Orders' table containing fields that apply to the entire order, such as OrderID (Primary Key), Cust_ID, Payment_ID, and OrderDate.
Create another table named 'OrderDetails' with OrderDetailID (Primary Key), OrderID (Foreign Key), IngredientType, IngredientID, and Quantity.
Transfer the comma-separated values from your old table into individual records within the new OrderDetails table. For example, if an order has two ice creams, insert two separate rows in OrderDetails tied to the same OrderID.
Go to the 'Database Tools' tab and click 'Relationships'. Drag the OrderID field from the Orders table to the OrderDetails table. In the Edit Relationships dialog, check the box for 'Enforce Referential Integrity' and click 'Create'.

Looking for a Lightweight Office Suite Alternative?
While database management requires specialized tools like Microsoft Access, handling reports, exporting data, and managing spreadsheets can be done effortlessly with WPS Office. WPS Office is a free, lightweight suite offering high compatibility with Microsoft Word, Excel, and PowerPoint files.
- 1. Export Access Data: In Microsoft Access, right-click your query or table and export it as an Excel (.xlsx) file.
- 2. Open with WPS Spreadsheet: Launch WPS Office and select WPS Spreadsheet to open your exported database file.
- 3. Analyze and Share: Use WPS Spreadsheet's built-in pivot tables and charts to analyze your normalized data without needing a database engine.

Frequently Asked Questions
Why can't I check the 'Enforce Referential Integrity' box in Access?
This usually happens if the data types of the related fields do not match perfectly (e.g., trying to link a Short Text field with a Number field) or if there is existing data in the foreign key table that does not have a matching record in the primary key table.
Can I use multi-valued fields in Microsoft Access relationships?
While Access provides a 'Multi-Valued Field' feature for specific web compatibility, it is highly discouraged in professional database design because it violates normalization rules, complicates queries, and prevents standard one-to-many relationship mapping.
What does database normalization mean in MS Access?
Normalization is the structural process of organizing data to minimize redundancy. In MS Access, this means ensuring each table serves a single clear purpose, and each field contains only one discrete piece of information.




