Does your Microsoft Office Access frequently showing “can’t create this relationship and enforce referential integrity” error?
You must be thinking why this error is frequently popping up on your screen?
To know more about this specific “referential integrity in Access” issue just check out the complete post.
This blog also covers detailed information regarding referential integrity, Purpose Of Enforcing Referential Integrity, How To Turn Referential Integrity On Or Off, fixes to resolve can’t create this relationship error, and much more.
Free MS Access Database Repair Tool
Repair corrupt MDB and ACCDB database files and recover deleted database tables, queries, indexes and records easily. Try Now!
By clicking the button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.
Practical Scenario:
I am asking this question from one of our users. I am not really up to date on Access. It is Access 2010 (32bit) on Windows 7 pro.
“The problem is that we need the “clientnickname” (this is the name we have given the field)field in the clients table to be related to the “clientnickname” field in the matters table, with a one-to-many relationship, enforcing referential integrity, with cascade delete and cascade update. I’ve done everything I can to make sure there are no records in the matters table that don’t relate to the primary key in the clients table, but it refuses to enforce referential integrity. This relationship has always existed in this db up until recently we noticed it had been deleted. In trying to recreate the relationship, this is the problem I’ve had.”
Source:
Error Details :
Name: Microsoft Office Access can’t create this relationship and enforce referential integrity
Description: Microsoft Access can’t create this relationship and enforce referential integrity.@Data in the table violates referential integrity rules. For example, there may be records relating to an employee in the related table, but no record for the empl
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 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. In order to see only tables, tap the Tables option.
- After then select one or more tables and then click on the Add option. Finishing 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. In order to drag multiple fields, you need to press the Ctrl key. After then tap to each field, and then drag them.
- This will open the Edit Relationships dialog box.
- Checks the fields name 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 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 references 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.
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 for 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 propagates referential updates and deletions. So that all the related rows get changed accordingly, check out the section which Set 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?
- Open the relationship window, and make a tap to the relationship line for the relationship which you want to modify. The selected relationship line will appear thicker after selection.
- Now make a double tap on the selected relationship line. This will open the Edit Relationships dialog box.
- Make a selection for the Enforce Referential Integrity check box.
- Do any additional changes to the relationship if it is needed.
- 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.
How To Fix MS Access “Cannot Create Relationships To Enforce Referential Integrity”?
In order to fix Microsoft Access Can’t create relationships to enforce referential integrity. 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.
This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.
Still having issues? Fix them with this Access repair tool:
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.