Fixed- Microsoft Access Can’t Create This Relationship and Enforce Referential Integrity Error!

In Microsoft Access databases, referential integrity is an excellent feature that ensures the relationships between tables remain consistent (every foreign key must match a corresponding primary key). When errors like “Microsoft Access can’t create this relationship and enforce referential integrity” appear, it often points to issues in the database structure.

Screenshot of the error:

In this blog, you will learn why this error actually occurs and how to fix it with less effort to maintain data integrity.

What Does Referential Integrity (RI) Mean?

Referential integrity is one concept of a relational database, which states that table relationships should always be consistent. Alternatively, it also means that any foreign key fields must match with the primary key which is referenced by the foreign key.

Thus any changes done in the primary key must be applied to all foreign keys. Likewise, the same restriction is applied to the foreign keys in that any update must be circulated to the primary parent key.

Example For Referential Integrity:

Consider a bank database having the two tables:

• ACCOUNTS_MASTER Table: This contains the basic bank account data like its account type, account holder, account creation date, and withdrawal limits.
• CUSTOMER_MASTER Table: It has the customer/account holder data like its social security number, name, address, and date of birth.

For the unique identification of each customer/account holder in the CUSTOMER_MASTER table, a primary key column named CUSTOMER_ID made.

For identifying customer and bank account relationships in the ACCOUNTS_MASTER table, an already existing customer in the CUSTOMER_MASTER table must be referenced. Thus, the CUSTOMER_ID column – also created in the ACCOUNTS_MASTER table – is a foreign key.

Well, this column is a special one because its values are not newly created. Rather these values must reference identical and existing values in the primary key column of another table, which is the CUSTOMER_ID column of the CUSTOMER_MASTER table.

Referential integrity is a standard that means any CUSTOMER_ID value in the CUSTOMER_MASTER table can’t be edited without editing the corresponding value in the ACCOUNTS_MASTER table.

Common Reasons for Can’t Create This Relationship and Enforce Referential Integrity Error

Below are the reasons that can lead to this annoying error:

1. Mismatched Data Types- If the primary & foreign keys have dissimilar data types, then you can face this error message.
2. Existing Records Violate Referential Integrity- Pre-existing data might already break referential rules.
3. Null Values in Foreign Key Fields- Foreign key fields hold null values that can hinder the relationships.
4. Lack of Indexes on Related Fields- MS Access needs indexes on fields participating in relationships. Missing indexes can lead to this error.

Also Read: How to Enforce Referential Integrity in Access Like a Pro?

How to Fix Microsoft Access Can’t Create This Relationship and Enforce Referential Integrity Error?

Try the below effective tips and tricks to solve this problem:

Solution 1- Verify the Data Types

The initial step you can take to fix cannot create this relationship and enforce referential integrity error is verifying the data types:

For this:

• Launch the table design view for both tables.
• Make sure the primary & foreign key fields have equal data types.

Existing data in the table violates referential integrity rules in the table.”

You try to create a relationship using the CONSTRAINT clause of the ALTER TABLE statement, but existing data in the two tables violates referential integrity constraints.

For eg: There must be a record relating to the employee in the related table but no corresponding record for an employee in the primary table. To make a relationship, you need to edit the data so that primary records exist for all relevant records.

Solution 2- Check for Invalid or Null Values

Sometimes, Null or invalid values in the database can cause can’t create this relationship. To fix this problem:

• Run the below query to identify the null values in a foreign key column.

sql

SELECT * FROM [ChildTable] WHERE [ForeignKeyField] IS NULL;

• You have to remove the invalid entries to ensure they align with primary key values.

How to Create A Relationship in Access Database?

• Go to the Database Tool tab, in the Relationship Group, tap to the Relationships.

• If the relationships are not defined, the Show Table dialog box automatically appears on your screen. But if it won’t appear, go to Design Tab>Relationship Group> click Show Table.
• Clicking on the Show Table dialog box will display all of the tables and queries in the database. To see only tables, tap the Tables option.
• After then select one or more tables and then click on the Add option. To finish the process of adding tables, tap the Close option.
• Drag a field mainly the primary key field from one table to the common field (foreign key) in the other field. To drag multiple fields, you need to press the Ctrl key. After that tap to each field, and then drag them.
• This will open the Edit Relationships dialog box.

• Check the field names shown are present in the common fields for the relationship. If a field name is incorrect, then click on the field name and select the best matches fields from the list.
• In order to enforce referential integrity in Access for this relationship, you need to choose the Enforce Referential Integrity box.
• At the end tap the Create option.
• After completing all this in the Relationships window, tap the Save option just to save your relationship layout changes.

What Is The Purpose Of Enforcing Referential Integrity?

How To Enforce referential integrity in Access  2013/2016?

The main purpose of using referential integrity is to prevent orphan records and maintaining the references completely synchronized. So that there is no record in your Access database that references other records that no longer exist.

Users can enforce referential integrity by enabling it to create a table relationship. Once it gets enforced, Access rejects any operations which violate referential integrity rule for that table relationship. Access also rejects the updates that change the target of references and also deletions that remove the target of references.

In order to have access to propagate referential updates and deletions. So that all the related rows get changed accordingly, check out the section which Sets the cascade options.

Condition Under Which Referential Integrity Works:

The referential integrity works only when the following condition matches:

• One of the linked fields the Access Database records is a primary key.
• Related fields must have the same data type and size.
• Both the tables are in the same Access Database.
• The same records in the related table are not allowed unless a matching record already present in the primary table.
• While establishing the referential integrity one common problem encountered by the users is Orphan data in a related table.

How To Turn Referential Integrity On Or Off?

1. Open the relationship window, and make a tap to the relationship line for the relationship that you want to modify. The selected relationship line will appear thicker after selection.
2. Now make a double tap on the selected relationship line. This will open the Edit Relationships dialog box.
3. Make a selection for the Enforce Referential Integrity check box.
4. Make any additional changes to the relationship if needed.
5. and then click OK.

Rules Set After Enforcing Referential Integrity

After enforcing referential integrity in Access, the following rules are set.

• You can’t add a record to the related table until a matching record already exists in the primary table.
• Can’t modify the value of the primary key in the primary table if the same record exists in the related table.
• Can’t delete records from a primary table if matching records exist in a related table.

Related FAQs:

What Is Relationship in Access Database?

Relationship in Access database helps you to combine data from two different tables. Well in an Access desktop database one can easily create a relationship in relationship windows.

How to Create a Relationship and Enforce Referential Integrity?

To create a relationship and enforce referential integrity, you have to double-click on the relationship line. The Edit Relationships dialog box appears. Now, select, create, or clear Enforce Referential Integrity then click OK.

Can We Edit or Delete a Relationship After Defining It Once?

Yes, you can edit or delete a relationship after defining it once.

Which Command Is Used to Remove a Relationship?

The delete command is generally used to remove a relationship in MS Access.

Final Thoughts

Above are some of the tested methods to troubleshoot Microsoft Access can’t create this relationship and enforce referential integrity. So, follow them one by one & get your job done instantly.

That’s all…!

Still having issues? Fix them with this Access repair tool:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.

1. Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
2. Click Browse and Search option to locate corrupt Access database.
3. Click Repair button to repair & preview the database objects.

Pearson Willey

Pearson Willey is a website content writer and long-form content planner. Besides this, he is also an avid reader. Thus he knows very well how to write an engaging content for readers. Writing is like a growing edge for him. He loves exploring his knowledge on MS Access & sharing tech blogs.