Referential Integrity
By Richard Rost
4 years ago
How to Set Up Referential Integrity in Access
In this Microsoft Access tutorial, I will teach you about referential integrity: what it is, why it's useful, and how to set it up. We'll also talk about the very dangerous "cascade deletes" option.
Links
 Keywords
access 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, What is referential integrity in MS Access, Enforce Referential Integrity, prevent deletion, cascade deletes, Enforcing Referential Integrity in Microsoft Access, Understand Referential Integrity in Microsoft Access, what is referential integrity
Intro In this video, we will talk about how to set up and use referential integrity in Microsoft Access. I will show you what referential integrity is, why it is important for maintaining consistency in your database tables, and how to enforce it to prevent orphaned records in one-to-many relationships, such as between customers and orders. You will see a step-by-step example of creating these relationships, setting up the necessary options, and testing them to keep your data reliable.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In this video, we are going to talk about how to set up referential integrity and what it is in Microsoft Access.
What is referential integrity? It is a logical dependency between two tables, used to maintain consistency in your data. Basically, the most important reason that you want to use it is because it guarantees no orphaned records.
What does all this mean? Let me demonstrate.
I have got two tables: a customer table and an order table. The customer table contains information on our customers - first name, last name, and so on. The order table has information on each order, including a customer ID, so we can track each customer for each order.
As you know from my previous videos, the customer is uniquely identified by his customer ID. That is called the primary key of the customer table. I cover that in Access Beginner Level 1. If you have not watched that video, go do that right now. Go watch that video if you do not know what a primary key is.
The customer ID is the primary key in the customer table. Now, we are going to use that customer ID to track who owns each order. So, the customer ID will be stored in the order table as well. There, it is called a foreign key. It is a key value, but it is not the primary key in the order table. The primary key of the order table is the order ID. That is the primary key for that table.
If you do not know about that stuff, go watch my Relationships video. I talk about setting up these relationships between multiple tables. I even talk a little bit about referential integrity in the extended cut for the members. We are going to talk about it right now.
Everything looks good so far. I have my customer table with all the customers. They all have a customer ID. I have got my order table. Each order has its own order ID. That is the primary key. There is a customer ID to represent the customer for that order. That is the foreign key.
Now, here comes the problem. What happens if Jim Kirk dies? He leaves the Nexus with Jean-Luc, the two of them go fight Soren. Kirk falls off a cliff to his death.
Now, I recommend you do not delete records. You mark them inactive. Rightly, have an isActive field. That is a whole different video. Look down below in the links section. I will put a link to my Don't Delete Data video. That is what I recommend you do. Do not ever delete data like this.
But let us say you did not know any better. You figure Jim Kirk is not going to be buying any more photon torpedoes from your store, so you delete his customer record. Now Jim Kirk's customer record is gone.
Do not ask me about getting that autonumber back. Autonumbers are not for you. Do not worry about it. Two is gone forever as far as you are concerned. It does not matter.
But now you have got three orders in your order table that do not have a customer associated with them. This could cause any number of logical problems in your database. The logistics could be all messed up depending on what queries or reports that you have. Maybe your year-end accounting needs some customer information, especially if you do not have left joins and so on. There are all kinds of things that this could mess up in your database. Maybe you need to look up what state he is from to pay sales tax. Who knows.
This is just one simple example of what can happen if you delete the parent part of a parent-child or a one-to-many relationship. That is bad.
So how do we fix this? We can set up referential integrity between these two tables so we can say, "Look, do not let the user delete a customer if there are related records in another table." How do we do that? Let me show you.
Here I am in my TechHelp free template. This is a free download from my website. If you want to grab yourself a copy, we have got a customer form that is based on the customer table, and there is an order form based on an order table. There is also an order details table to represent the line items. Here is another way you want to set up referential integrity between the order table and the order details table. You do not want to delete an order if it has detail items.
Let us set this all up.
Go into Database Tools, Relationships. Now your Relationships window is probably empty. Go to Add Tables. We are going to bring in the customer table and the order table. Now we are going to make a relationship between the customer ID here and the customer ID there. Start over here, click and drag and drop it on customer ID just like you are doing in a query.
Now, the Edit Relationships window pops up. Click on this option right there that says Enforce Referential Integrity. That is going to mean you cannot delete one of these records if a matching record exists over here.
There are some other options. I talk about these in my full classes. Be very careful with options like Cascade Delete, for example. Cascade Delete means if you delete a customer, it will delete all of his orders. That is very dangerous. Do not use that. There are some instances where you do want to use it, such as with temporary data, but just trust me, do not touch those options.
Click Create. You will see this relationship pops up there. Now you have created a relationship with referential integrity. Let us go test it. Save this (Control+S) and close it.
Go to the customer form. There is Richard Rost. Go to Jimmy Kirk (we are going to mess with him). Jim Kirk, go to Orders. He has got some orders in here, three of them in fact. Close that. Now, let us try to delete Jim Kirk. Delete. You cannot do it. This record cannot be deleted or changed because the table OrderT includes related records. You can still make changes to some of the other fields. You can change his name, but you cannot change this. You can have a primary key that is not an autonumber. I do not recommend it, but you can. That is what the "changed" part means. You cannot change that field. That is what Cascade Updates is for, which you will never use. Trust me.
There is a lot to this. I spend hours and hours covering all this in my full course. I am just giving you the basics.
You might want to do the same thing between orders and order details. You do not want to be able to delete an order if there are details on it. Let us set that up too real quick.
Database Tools, Relationships. You already have OrderT in here, so we are going to add OrderDetailT right over here. Set the relationship up using order ID, same way. Enforce referential integrity and click Create. Save it, close it.
Now come back into here. Go into one of Jim Kirk's orders and now try to delete the parent order. Delete. The same thing. See?
So there you go. That is referential integrity in a nutshell. Use it to prevent deleting parent records in a one-to-many relationship. There are lots more uses. I talk about it a lot more in my Access Expert Level 2 class. We go into a lot more examples and different topics - all the nitty gritty.
If you have any questions, feel free to post them down below. I hope you learned something and we will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
All of our members will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me. You will get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free.Quiz Q1. What is referential integrity in Microsoft Access?
A. A logical dependency between two tables to maintain data consistency
B. A method for increasing the speed of data entry
C. A form of data encryption for sensitive records
D. A way to automatically create forms and reports
Q2. Why is referential integrity important?
A. It guarantees that no orphaned records exist
B. It makes database backups faster
C. It allows for duplicate primary keys
D. It increases the storage capacity of your tables
Q3. In a typical customer-order relationship, what is the primary key in the customer table?
A. Customer ID
B. Order ID
C. Customer name
D. Order date
Q4. What is the purpose of the customer ID field in the order table?
A. To act as a foreign key that tracks which customer placed each order
B. To uniquely identify each order
C. To store the customer's phone number
D. To calculate order totals
Q5. What is a foreign key?
A. A field in one table that links to the primary key in another table
B. Another name for an autonumber primary key
C. A field that only contains text data
D. A field used for storing passwords
Q6. What happens if you delete a customer who has related orders and referential integrity is NOT enforced?
A. The orders remain but have no associated customer, causing orphaned records
B. All related orders are automatically deleted
C. The database prevents the customer from being deleted
D. The database automatically fills in a default customer
Q7. What is the recommended approach when a customer is no longer active?
A. Mark them as inactive using a field, not delete their record
B. Delete the customer's record permanently
C. Change their primary key value
D. Move all of their orders to another customer
Q8. What does the "Enforce Referential Integrity" option do in the Relationships window?
A. Prevents the deletion of a parent record if related child records exist
B. Automatically merges duplicate records
C. Adds new records when a parent is deleted
D. Encrypts all associated data
Q9. What is the danger of using the Cascade Delete option?
A. It can delete all related records without warning, causing data loss
B. It prevents any records from being deleted
C. It causes duplicate records to be created
D. It makes backup copies of all data
Q10. When building relationships, which fields should be connected between the customer and order tables?
A. Customer ID in both tables
B. Customer name and order date
C. Order ID and order total
D. First name and last name
Q11. Which action is NOT allowed when referential integrity is enforced and related records exist?
A. Deleting a parent record (such as a customer with orders)
B. Changing non-key fields in the parent record
C. Adding new child records (such as new orders)
D. Viewing related records
Q12. If you want to prevent deleting an order that has related order details, what should you do?
A. Enforce referential integrity between the order table and the order details table
B. Use only forms for data entry
C. Remove all relationships
D. Disable primary keys
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone explores setting up and understanding referential integrity in Microsoft Access.
Referential integrity is a concept in relational databases designed to maintain consistency between tables. It ensures that relationships between related records in different tables remain consistent, helping prevent issues like orphaned records.
To illustrate, let me use an example of a customer table and an order table. The customer table stores details about each customer, such as their first and last names. The order table contains information about each order, linking each one to a customer using a customer ID. In this scenario, the customer ID acts as the primary key in the customer table, uniquely identifying each customer, while that same customer ID appears in the order table as a foreign key to determine which customer placed each order. The order table has its own primary key, the order ID, which uniquely identifies each order.
If you are not familiar with the concepts of primary and foreign keys, I recommend watching my Access Beginner Level 1 class for an introduction, as well as my separate Relationships video for a full discussion on connecting tables and establishing these keys.
Now, here is where problems can arise without referential integrity. Imagine a situation where a customer, say Jim Kirk, is deleted from the customer table. If any orders are linked to Jim Kirk, those order records will be left without a corresponding customer, resulting in orphaned records. This creates all kinds of potential issues, including problems with reports, queries, or accounting tasks that rely on the customer information associated with each order. This is just one example of how deleting a record from the parent side of a one-to-many relationship can cause trouble.
To prevent these problems, we set up referential integrity between tables. This way, the database will not allow the deletion of a customer if related orders exist in the order table. The same principle applies to connected order and order details tables, where we do not want to delete an order if it still has order details linked to it.
To establish referential integrity, open the Database Tools menu and go to the Relationships section. Add the appropriate tables to the relationships window. Then, create a relationship between the customer ID field in both tables. In the Edit Relationships window that appears, select the option to Enforce Referential Integrity. This setting tells Access not to allow deletions of parent records if related child records exist.
There are additional options in this window, such as Cascade Delete. Be very careful with these advanced options. Cascade Delete will delete all related child records when you delete a parent record. For most circumstances, this is not what you want, especially with important data like customers and orders. In rare cases, with temporary or unimportant data, cascade options might make sense, but I advise avoiding them unless you truly understand their consequences.
After creating the relationship with referential integrity enforced, you can test it by trying to delete a customer who has related orders. Access will prevent you from performing the delete, displaying a message that the related records exist. The same applies if you try to delete an order that still has related order details.
Referential integrity is an essential tool for preserving reliable, organized data in your databases by making sure that parent-child relationships are maintained. I cover this topic in greater depth in my Access Expert Level 2 class, where I discuss more advanced scenarios and related options.
If you have any questions, I invite you to post them. I hope this overview of referential integrity helps you maintain more robust and accurate databases.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Definition of referential integrity in Access
Preventing orphaned records with referential integrity
Primary keys and foreign keys explained
Setting up relationships between tables
Steps to create a relationship in Access
Using the Relationships window in Access
How to enforce referential integrity
Testing referential integrity restrictions
Preventing deletion of parent records with related data
Dangers of cascade delete and why to avoid it
Applying referential integrity between orders and order details
Blocking deletion of orders with associated details |
