Relationships and referential integrity

- [Instructor] Once our data tables have primary and foreign key fields created, we need to formalize that relationship and let Access know which fields relate to which. We can do this with the Relationships Tool and you can find it in a couple of different places. Typically, you're gonna come up here to the Database Tools tab, and we'll find a button here for Relationships. We can also find it in the Table Contextual Tab. Let's go ahead and open up one of our tables. I'll just double click on the Guest Table here to open it. And in the Table Contextual Tab, we'll see another button here for Relationships and this will get us to the same spot. However, in order to modify or create relationships between tables, those tables need to be closed. So even though you can get to the Relationships Editor through the Table Tab, it'll often create a situation where you can't actually edit it once you get there because the original table is still open. So let's go ahead and close out all of our tables. I'll just press the X on the far right side of the tab bar. And then we'll go up to Database Tools and click on Relationships here. The Relationship screen is essentially a blank canvas, here in the background, where we can lay out a map to all of our tables and show how they relate to one another. When we first opened it, we get a new show table window here that lists all the different tables that exist in our database. Notice that we also have the option to pull in Queries and we have a tab here that'll list both Tables and Queries. Let's go back over here to the Tables Tab. In order to add in one of the tables, into our relationships map, simply select it in the list and press the Add button. Another way we can do that is to, just simply, double click on it in the list and you'll see that it gets added here, in the background, as well. When we're done adding our tables in, go ahead and press the close button or you can press the X in the upper right hand corner. If you need to get the show table window back again, there's a button for it on the Design Contextual Tab, here in the Relationships Group. Let's go ahead and close out again. Now each of these squares, in the Relationships Map, represents one of the tables in the database. We have one here for the Guest Credit Cards and one over here for the Guests. We can rearrange these anywhere we want, just by dragging dropping them on the screen Below the name of the table, here at the top, we have a column that lists out all the different fields of data that appear in those tables. Right now the Guest Table is a little bit large. I have a scroll bar on the right. I can resize this square, make it a little bit bigger, so it won't have that scroll bar. Just by clicking and dragging on some of the edges. Let's go ahead and move the Guest Credit Cards Table, down just a little bit more. To join these two tables together and formalize the relationship, simply find the primary key in the primary table and drag and drop it on top of the foreign key that we saved inside of the Guest Credit Cards Table. You can identify the primary key, based off the key icon on each of these tables. So let's go ahead and drag the ID field from the Guest Table and I'll drag and drop it on top of the Guests ID field any Guests Credit Cards Table. When I do that I get a new edit relationships window that pops open. Down below it'll confirm which fields are related to each other. Right now I've got the Guest Table and the ID field of that table, is related to the Guests Credit Cards Table and the Guest ID Field in that table. If I press the Create button, that'll remove that window from the screen and we'll see that we now have a new, black join line between the two tables. Now Access knows that they can use the values from one record in one table to find related details in the other table. If we need to get back and edit that relationship simply double click on this join line. Now, the targets a little bit small, so If you accidentally click off of it a little bit, you'll get a blank edit relationships window. Let's go ahead and press cancel to this and make sure we're actually double clicking right on that line. One very important option that we can get to in the screen, is the Enforce Referential Integrity checkbox. Let's go ahead and turn that on. When this is checked, Access won't allow you to create records at a table when the foreign key, doesn't match a record in the other table. In our example here, when I turn Enforce Referential Integrity on, Access won't allow me to put a credit card that isn't attached to a valid guest. It will also prevent me from deleting a guest, while accidentally leaving their credit card info in the database. In other words, it helps us prevent orphan records. Records that don't relate to any parent info. This is a really great control to have on when appropriate because it ensures that your database remains reliable. When force Referential Integrity is turned on, you notice that we get two additional options, Cascade Update Related Fields and Cascade Delete Related Records. Both of these deal with situations where the data changes. If you change your primary key, then Cascade Update will also make the same change to the foreign keys and all the related tables. This will make sure that records still point to each other properly, after a change. Cascade Delete will delete related records, when the parent is deleted. In our case, if I turn that option on, then delete a guest, Access will automatically find their credit card records and delete those as well. Both of these options make maintaining your database really easy. On the bottom of the Edit Relationships window, you'll notice the text One To Many. This is the type of relationship that we've created where each one guest may have many credit cards. This is by far the most common relationship type. The other type that you'll see here is a One to One relationship, where each parent record has, at most, one child record. When I press okay with enforce referential integrity turned on, you'll also notice that we get this one to many symbolization here on the line. This reinforces the notion that we have a one to many relationship between our guests and our credit cards. Now, if a guest wants to save dozens of different credit cards with us, it's no problem. We've set up our database to handle that kind of situation. Now before I end this movie, I wanna share one common tripping point. Notice on the Relationship Screen that we have scroll bars on the bottom and the right side. And these can indicate that the relationships map can be quite large. But notice that if I actually click on these to scroll them off, you notice that, it looks like, our tables have disappeared from the relationship screen. It's really easy to accidentally scroll away from any tables you may have placed and think that they've disappeared. But don't worry, they're still there. You just need to go ahead and make sure that your scroll bars are tucked at the very top and on the far left side of the screen. When you're done editing the relationships, go ahead and close the relationships tab. Access is going to prompt us to save the changes and I'll say yes. So that's how you establish relationships between tables. Anytime you create a new data table, you'll wanna revisit the Relationship Screen to tell Access how the new table integrates with the others.