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. You can find it in a couple of different places. Normally, you're gonna go up here to database tools and you'll find it right there. But, if you also have a table open, I'll go ahead and just open up the Guest Table, in the Table contextual tab, you'll notice that we have another button for it right there. Either way, it starts the same tool. However, in order to modify or create relationships between tables, those tables actually need to be closed. So, even though you can get to the Relationships editor through the Table tab, it will 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 make sure all of our tables are closed and then we'll go up to the Database Tools tab and click on Relationships. The Relationships screen is essentially a blank canvas here in the background where we can layout a map of all the tables and how they relate to one another. There's a new contextual tab at the top called Relationships Tools Design and on it is a button called Show Table, which opens up the window that automatically opened up right here. Inside of the Show Table window, we get a listing of all the different tables that currently exist inside of our database. We can also see all of the Queries that exist and the listing of both Queries and Tables. Let's switch back to my Tables tab and I'm gonna add both of these tables into my Relationships Map. You can either select them one at a time and press the add button, or you can simply double click on them. When you've added both, go ahead and close the Show Table window. Another way to add tables into the Relationships map is to simply drag them out of the Navigation pane and drop them on the middle of the screen. If you need to remove a table, just right click on it and choose Hide Table. Now each of these squares represents a table from our database. We have the name of the table across the top and then a listing of all the different fields down the bottom. We could move these around on the map anywhere we want and we can even resize it if we need to, in order to see all the different fields. To join these tables together and formalize a relationship, simply find the Primary Key in the Guest table, you'll notice it actually has this ID field here with the key icon, and we're gonna drag and drop it onto the related field in the Guest Credit Cards table. Go ahead and drag and drop it right down there. That'll automatically open up the Edit Relationships screen where we can confirm the fields that are related together. From the Guest table, I have the ID field and from the Guest Credit Cards table, that matches to the data in the Guest ID column. If you press the Create button, that will go ahead and create that relationship, and you'll see that we now have a joining line between those two fields. In order to get back to the Edit Relationships window, just double-click on the line. Now it's a really small target, so if I go ahead and close this window, if you accidentally miss it, like click over here, you get the same Edit Relationships window, but it will be blank. So then you cancel out of this and just make sure you're double-clicking right on that line. One very important option you can get to from this screen is the Enforce Referential Integrity option right there. When this is checked, Access won't allow you to create records in a table when the foreign key doesn't match a record in the other table. In our example here, when I turn on Enforce Referential Integrity, Access won't allow me to input a credit card that isn't attached to a valid guest. It'll also prevent me from deleting a guest while accidentally leaving their credit card information in the database still. In other words, it helps 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. Once Enforce Referential Integrity is turned on, you'll notice we have to additional options down below: Cascade Update Related Fields and Cascade Delete Related Fields. These deal with situations when the data changes. If you change a Primary Key, then Cascade Update will also make the same change to the Foreign Keys in all related tables to make sure that the records still point to each other properly. 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 very easy. On the bottom of the Edit Relationships window, you'll notice the text One to Many. This is the type of relationship 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, only one child record. Once I've turned on Enforce Referential Integrity, and pressed OK, you'll also notice this One to Infinity symbol, here on the joining line. This indicates that this is a one to many relationship. So now, if a guest wants to save dozens of different credit cards with us, it's no problem. We set up our database to handle just that situation. Now before I end this movie, I want to share one common tripping point here with the Relationships screen. You'll notice that it has these scroll bars on the far right and the bottom of the screen. The Relationships Map can be quite large and if you accidentally click in this area, you'll notice that everything just zooms right off the screen. Sometimes people will think that they've accidentally deleted everything and they'll start dragging in their tables once again to rebuild it. Let's go ahead and right click, and I'm just gonna hide that table again. If that ever happens to you, just make sure you keep an eye on the scroll bars and make sure that you're starting in the far left hand position and at the very top to make sure that you're seeing the beginning of your Relationships screen. So that's how you establish relationships between tables. Any time you create a new data table, you'll want to revisit the Relationships screen to tell Access how the new tables integrate with all of the others.