Next up is a review of foreign key constraints, and we'll use this example of Films, and Inventory, and Customers, and Rentals. But first, to make this really easy to understand, I'm going to switch to a brand new database called Blog, and we'll use the common example of Posts, as well as Comments. Really easy to understand. So let's say, for Posts, I'll go to my structure, and we'll say, well, yes, a post requires a title, and this will be a Varchar, and the max is 255. Next, can it be null? No. After that, of course, you'd want the body of the post, timestamps, whether it is archived,

No. After that, of course, you'd want the body of the Post, timestamps, whether it is archived, a number of things like that. But we'll keep it very, very simple. Next, for the Comments table, well, yes, you need the body of the Comment, and this can be as long as you want. So we'll use a text type there, and it cannot be null. But also, to which Post does this Comment belong to? Sounds like we need a reference there. So we'll say, okay, I need to store the postId that is related to this Comment, and that

Sounds like we need a reference there. So we'll say, okay, I need to store the post ID that is related to this Comment, and that will be an integer, unsigned, which effectively means positive, so that number will never be negative 10, in other words, and it cannot be null. Okay, so think about this. Now, yes, I could add a Post called My First Post, and then I could add a Comment, and I could say here, Great Post! And then reference the post in question. Okay, so now think about it. I can see that this Comment, called Great Post, belongs to the Post with an ID of 1.

Okay, so now think about it. I can see that this Comment, called Great Post, belongs to the Post with an ID of 1. Or in other words, I could say, select all comments where the post_id is equal to the post in question. And often, this will be something you fetch either from a query string or from the URI. And sure enough, I can see only the comments associated with this post. However, we have one problem here. What is keeping me from setting this post_id equal to something that doesn't exist? Nothing. Nothing at all.

Nothing. Nothing at all. So now think about it. I have a Comment associated with a Post that does not exist. There is no Post with an ID of 99. So we refer to this as an orphaned record. And the name is appropriate because we don't know what this refers to. The Post ID of 99 does not exist. Or in other words, we fractured the integrity of our database. Now there are solutions to this.

Or in other words, we fractured the integrity of our database. Now there are solutions to this. So take a look. I'll bring this back to the original Post. And we'll set up a foreign key constraint. Now I'll start by showing you the SQL route, but you can also do it directly through the GUI. Or, of course, as a typical database migration if you're using a migration file. Okay. So let's say we're going to alter an existing table called comments.

Okay. So let's say we're going to alter an existing table called comments. So we're going to update this comments table. And I'm going to add a foreign key. And specifically, which one is it? Well, it's that post_id column, right? That post_id column references the ID column on the post table. Okay. The post_id column references the post table and specifically the ID column on that post table.

The post ID column references the post table and specifically the ID column on that post table. Now we'll leave it like that and I'll hit command R to give that a run. And we'll know we did it correct because if I go back to the comments table and I click on relations, you will see a new relation tab here. So what does this mean? Well take a look. Now if I try to update this post ID to something that does not exist, it will not let me. Cannot add or update a child row because a foreign key constraint failed. This is what we want.

And if you think about it, what actually happened is nothing. We took no action at all because this rule was in place. Well, as it turns out, we can tweak this in a number of ways. So take a look at this. We're going to start from scratch. So I could manually do it by saying ALTER TABLE comments and then DROP FOREIGN KEY. And we'll have to reference the name and you can find it here. Or of course, you can override the default name. In this case, it's comments_ibfk1. So we'll do it manually here, but also you're in a GUI.

In this case, it's comments IBFK1. So we'll do it manually here, but also you're in a GUI. So you could have selected the record and hit the minus sign down at the bottom. Just trying to show you both techniques. Anyways, if we do it one more time, so I'll replace it with what we had earlier. But now I'm going to add this setting here on delete. So take a look at this clause here. Let's read it out. We're going to change the comments table to add a foreign key called post_id. And that post_id references the id column on the posts table.

We're going to change the comments table to add a foreign key called post_id. And that post_id references the ID column on the posts table. And if I were to delete that post in question, what should happen to the comment? Well, I've set it to cascade here. And the way I think of that is cascade down and delete all comments that are associated with that post. Okay, so now take a look. We'll go back to the content. Right now, the post with an ID of one has one comment. All right, let's try deleting the post.

Right now, the Post with an ID of one has one Comment. All right, let's try deleting the Post. It lets me. And further, if I now go back to the comments table, it cascades down and it deletes all Comments associated with that Post. And again, this is specifically because of this clause here. If you delete the Post, cascade and delete all related Comments. Now we also have options like on update as well. And you have roughly five different options to choose from. But in your day to day, it will often be one or two.

There was no action there because it was disallowed. There's also things like setNull. And that would update the foreign key to be null if that's appropriate. And there's a couple others. But again, I don't want to overwhelm you. You can research those if you want. Now here's one thing to be aware of, though. Let's go back to our post table and we'll bring it back, my first post, and we'll bring a new comment as well. Whoops, I guess that's now two.

a new Comment as well. Whoops, I guess that's now two. Yeah, the Post has an ID of two. Okay, now what if I were to change this Post ID to something different, like 100? Well it's not going to allow that either. Let's tweak it. So this time we're going to start from scratch one more time. So I will delete that through the GUI and we'll add it one more time. And we'll say, if you delete the Post, cascade down and delete all Comments. But if you update the Post, what should we do?

And we'll say, if you delete the Post, cascade down and delete all Comments. But if you update the Post, what should we do? Should we cascade as well? Should we restrict the operation? Should we set null as we learned? It just depends on what you want. Let's set cascade for both. All right, now back to the Relations tab, you'll see cascade is set for both actions. So this time, if I update the Post to how about 200, it will let me because it's going to cascade down to all related Comments and update their foreign keys to point to that.

So this time, if I update the Post to how about 200, it will let me because it's going to cascade down to all related Comments and update their foreign keys to point to that new ID. And remember, from a practical standpoint, the entire purpose of this is to retain some level of integrity in your database. You don't want a situation where you have orphans all over the place and potentially hundreds and thousands of records that have relationships to other tables and other records that simply don't exist. We don't want that to ever happen. Okay, so finally, we have been doing it the SQL route.

We don't want that to ever happen. Okay, so finally, we have been doing it the SQL route. But of course, if you want to do it directly in the GUI, you would simply set it up here. So you could either set a specific name, like often people will say, FK for foreign key. And then you'd reference the tables and then the name of the foreign key, which would be post_id in this case. Or if you don't include a name, MySQL will generate one for you. But anyways, I can say on the comments table, this post_id references the post table and specifically the ID column on that post table. If you were to delete the post, cascade down.

specifically the ID column on that post table. If you were to delete the post, cascade down. And if you were to update the post, cascade down. And that's very common. But yeah, there will be other situations where you want to say, no, you cannot delete this other record if I have these. It simply can't be allowed. There will definitely be situations when you opt for that. Okay, but anyways, if we did the same thing, now we have our constraint. Okay, so now, really quick, let's switch back to our Sequela database.

This is how we record all rentals. Now it stands to reason that we don't ever want a rental to be associated with a Customer who doesn't exist. So if I were to change this to some customerId that doesn't exist, it's not going to let me for that exact reason that we learned about. Let's take a look at what we have here. I'll go to the Relations tab, and sure enough, I can see for the customerId, we have a constraint or a rule. Let's read it. If I put a customerId column on this rentals table, so this one right here, that references

Let's read it. If I put a customer_id column on this rentals table, so this one right here, that references the customer table and specifically the ID column. So that references this right here. Okay, let's switch back. Now if we were to update the customer, we will cascade down and update the rental in the process. So we've already learned this. We've learned if I find, let's grab one real quick, this customer with an ID of 130. So let's go to 130.

We've learned if I find, let's grab one real quick, this Customer with an ID of 130. So let's go to 130. All right, Charlotte Hunter. Let's update her ID to 20,000. Okay, well because we have that rule here to cascade down, that means the associated Rental will now be updated as well automatically for you. Okay, now if we go back, you'll see for undelete, I don't see anything here. That means one was not specified, so it will default to no action. Or in other words, it will not allow you to change it. It's not going to perform any action here.