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…

Quick Navigation:

• What Is Referential Integrity?
• What Are the Conditions to Enforce Referential Integrity in MS Access?
• Why Is It Important to Enforce Referential Integrity?
• Once You’ve Established the Referential Integrity
• How To Enforce Referential Integrity in Access?
• How To Solve Access “Can’t Create Relationships for Enforcing Referential Integrity”?
• Frequently Asked Questions:

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.

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.

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

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.