Enforce Referential Integrity in Access [Step-By-Step Guide]

When you try to make a relationship between the 2 tables in MS Access, it’s usually a stupendous idea to simply enforce the referential integrity. Though, Referential integrity tracks accurate data and even guarantees that you don’t delete or change the related data accidentally in any of the tables. Thus, if you want to know how to enforce referential integrity in Access then read this article till the end.

Besides, here you will also learn what is referential integrity along with examples and why is it important to enforce referential integrity.

So, without any further ado, let’s get started…

Fix Microsoft Access Issues
Run a database Scan with Stellar Access Database Repair Tool to repair inconsistent Access database. After the scan is complete, the repair process will restore the database to its normal state fixing all the errors.

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.

Quick Navigation:

What Is Referential Integrity?

It is really important to know about Referential integrity in brief before you enforce referential integrity in Access.

Well, the Referential integrity states a relationship between the two tables. As each database table must have the primary key, and this primary key will appear in the other tables due to its relationship to the info in those tables.

However, when the primary key of one table seems in another table, then it’s known as the foreign key.

Now, let’s take a look at some examples of the Referential Integrity:

Here we will take the example of the bank database that has two tables:

  • CUSTOMER_MASTER Table: This holds customer or account holder data such as name, address, & DOB (date of birth).
  • ACCOUNTS_MASTER Table: This holds the elementary bank account info like account holder, account type, account opening date, etc.

What Are the Conditions to Enforce Referential Integrity in MS Access?

There are a few conditions that need to be met to use the referential integrity in Access, they are as follows:

  1. It is mandatory that one of a linked fields ought to contain the primary key.
  2. Both the tables should be in an identical Access database.
  3. The linked fields ought to contain the same type of data & size.
  4. You can’t add the same record in the related table that is already added in a primary table.

Why Is It Important to Enforce Referential Integrity?

It is necessary to enforce referential integrity because it confirms the relationship between 2 tables and keeps syncing during an execution of an update & delete instructions.

On the other hand, we can say, Referential integrity tracks the accurate data and guarantees that you don’t delete or change the related data accidentally. That is why it is important to enforce Referential integrity in Microsoft Access.

Also Read: Microsoft Access “Cannot Find The Referenced Form”

Once You’ve Established the Referential Integrity

After establishing the referential integrity successfully, the below guidelines are set:

  1. The first thing is- You cannot make changes in a value of the primary key if the same records exist in both related tables.
  2. The second thing is- You cannot add or delete any record from the primary table when matching records already exist in the related table.

How To Enforce Referential Integrity in Access?

Here you have to follow the below step-by-step instructions for how to enforce referential integrity between two tables in Access. To do so, you have to:

  • First, click on the tab “Database Tools”.
  • Then, click on Relationships option.

Enforce Referential Integrity in Access

Here, you need to specify a table that you wish to link together.

  • After this, click on Show Table option & add the tables to a Relationship window.
  • At this time, click on a related field of the primary table & drag it to a related field of a second table.

Enforce Referential Integrity in Access

  • Next, check both the options “Enforce Referential Integrity” and “Cascade Update Related Fields”.

Doing this will ensure that whatever changes you will make in the first linked field will be updated in a corresponding table field automatically.

  • Also, you have to check the box “Cascade Delete Related Records”.

Doing this will ensure that whatever record you’ll delete in the first linked field will be deleted in a corresponding table field automatically

  • Finally, click on the Create.

Enforce Referential Integrity in Access

Now, MS Access creates a relationship between two tables & enforces the referential integrity between both of them.

How To Solve Access “Can’t Create Relationships for Enforcing Referential Integrity”?

To solve Access “can’t create relationships for enforcing referential integrity” you can attempt to create the relationship with the CONSTRAINT clause of an ALTER TABLE statement.

However, do remember that current data in two tables interrupts foreign key constraints or referential integrity constraints.

Frequently Asked Questions:

What Are 2 Of the Mechanism That Can Be Used to Enforce Referential Integrity?

The two foremost mechanisms that can be used to enforce referential integrity are- Triggers and Constraints.

What Are the Three Types of Rules for Referential Integrity?

Whenever you proceed to do any work in any field then there must be some rules & regulations that needs to follow for completing the task successfully. And same applies to referential integrity.

Well, there are 3 sorts of main rules for referential integrity, they are as follows:

  1. INSERT rule
  2. UPDATE rule
  3. DELETE rule

Which Key Is Used for Referential Integrity?

The foreign key constraint is mainly used for referential integrity. It is also known as a referential integrity constraint or a referential constraint.

Time to Sum Up:

Hopefully, now you’ve learned how to enforce referential integrity in Access or how to enforce referential integrity between two tables in Access.

So, all you need to do is to follow the instructions mentioned above in this post and see the result.

Last but not the least, if you liked this blog then don’t forget to share it with others who want to enforce referential integrity in MS Access.

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.