Enforcing referential integrity with the Laravel Schema builder
Playing
01. Enforcing referential integrity with the Laravel Schema builder

Episodes

0%
Your progress
  • Total: 12m
  • Played: 0m
  • Remaining: 12m
Join or sign in to track your progress

Transcript

00:00
The benefit that we get from working within Laravel, and particularly with Laravel migrations, is that we can enforce referential integrity within our database table.
00:10
Now, that sounds a little bit complicated, a little bit boring, but in actual fact, it really makes sense. And since it's so easy to do, we may as well learn how to do it. So what we're going to do is start by creating some migrations, and then we're going to look at how we can relate things together so we enforce referential integrity, or we have
00:32
referential integrity. So referential integrity is basically being able to make sure that things that are related in your database actually exist. So for example, you may have users, you may then have posts, or likes, or statuses, or just anything. And what you want to do is make sure that you don't have, say, posts lying around that belong to a user that doesn't exist.
00:59
And you might be thinking, well, this doesn't really matter too much. But let's say you were listing out a load of posts, and you wanted to then display the user. Well, if the user doesn't exist in your database, you might see errors. And that will lead you to do things like, well, if that user exists, so if post user, then do something else, do something else, and things
01:24
kind of get a little bit complicated. So we're going to start off by generating a user's migration, and then we're going to generate a post migration, and we're going to look at actually how we relate these things together. So I have a fresh Laravel installation hooked up to my database, which I've already run migrations, but we're not actually tracking any at the moment.
01:44
So let's go and just create a migration here. So we say PHP artisan make migration. I'm going to go ahead and create a user's table, and we can use the create flag here to bootstrap everything up for us. So let's head over to the create user's table migration. And here you can see that a lot of it's been created for us. So let's say we have some kind of username. So this would be a string
02:10
here, and we could say that's length of 20. We already have an incrementing primary key, which is obviously important. We want to uniquely identify each of our users. And just for now, I'm going to set nullable timestamps. So we're going to be fiddling around manually within our database. So this will do for now. Let's go and generate another migration. So make migration,
02:36
and we'll create a posts table. And again, we'll supply the create flag there as well. Let's just correct that like so. So let's head over to the create posts table migration. And here we would obviously have, let's just say a string or some text with a body. We would have an integer
03:04
with a user ID. So you might think, well, this is fine. When we go and migrate these, so let's just go and do that now. PHP artisan migrate. Then we see these two tables. We can go ahead and create some kind of model for these. And for example, we could access a posts relationship on our user model and insert a post. Great. But what happens when that user
03:29
is deleted? Well, in that case, obviously the post will remain. And also the fact we don't actually know that that particular user exists. This is less of a problem. However, it still is a problem, especially when your application grows a lot larger. So I'm just going to roll back my migrations. And we'll look at enforcing this referential integrity using a foreign key.
03:52
Using a foreign key. So you want to do this from the table here. So for example, user has many posts, for example. We want to go and do this in here. And with Laravel, like I said, the schema builder makes this really easy. All we do is say foreign. We give the name of the foreign key. That's user ID. So this is our foreign key. And then we say it references
04:18
and we choose the column that it actually references. So in our case, it's it references the ID of the user, but we don't know which table. So we just say on users and we give the name of the table. It is as simple as that. So we have a foreign user ID key. It references the ID on the user's table. So if we now go and run our migrations, so let's say PHP artisan migrate,
04:45
we actually get an error here. And that's because what we need to do is set our user ID as an unsigned integer. So let's just go and roll back our migrations. And here we may have to delete this post table manually so we can get this right. So let's go and get rid of that. And let's set our ID or user ID to an unsigned integer. And let's go and migrate again.
05:09
There we go. So we now have migrated. We had a little problem there, which we had to manually delete the table, but that's fine. When you're kind of setting up your migrations, there's nothing wrong with kind of fiddling around with things. We now have our two tables. Nothing looks different. But let's just go and create a user. Let's go and create a post. So let's just type
05:31
test in here and go and reference user ID one. So let's just edit this user ID one. And I'm getting an error here. That's why I set nullable timestamps in my user table. I'm going to go ahead and pop them in. So now I'm using SQL Pro. We can actually see that under the post table, we get a little arrow. We can click that and SQL Pro will take me through a filter by this user ID.
05:57
So that's pretty useful on its own. What happens if we go and create a post that belongs to a user that doesn't exist? So I'm going to go and put my current timestamps in here as well. You can see here, cannot add or update a child row, a foreign key constraint fails. So this is perfect. We can't insert this record because there is no user with the ID of six,
06:22
which I just attempted to insert. So this is great. However, to really enforce this referential integrity that we've been talking about, what we want to do is make sure that, for example, when a user is deleted, so I'm just going to go ahead and delete this here, we can see that first of all, this isn't actually doing anything. But essentially,
06:44
what we want is our end goal is when a user is deleted, we want to delete all of the posts. Now, this might not be the case, and that's absolutely fine. If you don't want this to happen, there's no need to go any further and reference this within your migrations. However, what we are going to do is roll back and take a look at how this works. So let's just roll back our
07:03
migrations. We got rid of them two tables. And let's head over to our post table. I'm just going to set these two nullable timestamps so I can fiddle around with them manually. So when we define our foreign key constraint, what we can actually say here is on delete, we want to cascade. Now, obviously, cascading is just kind of filtering down. And all this means is that when our user
07:27
is deleted, this cascades through to our posts table. So it's as easy as that. The actual SQL to set this up normally would be a little bit more complicated. But using this, it reads really nicely. So let's go and run our migrations again. And let's head over to our database table. Let's just go and create a user very quickly. Let's go and create a post very quickly.
07:54
And obviously, we need to edit this and give a user ID. Like so. And now, if I go and delete the user, it lets me now. But what's actually happened is it's cascaded through and deleted that post. So for posts, this might not be a requirement. You might say never delete a user. But you might have other relationships where this works really well.
08:19
So for example, if a post is deleted, you might think, well, I don't want any likes or comments to be then associated with that post. And it just keeps your database nice and clean. So in order to actually reference that, you would do exactly the same thing. So let's take a look at doing that now. All we need to do is just very quickly generate a new migration. So make migration. Let's create a
08:43
comments table. And again, just supply our create flag. So comments. And let's go and edit this. So again, I'll just for now set nullable timestamps. And here, we might have a string with a body. And in here, we might have a user. And again, we'll set that unsigned. And then, of course, we wouldn't normally do this because we
09:18
would have some nicer way, like polymorphic relationships to set up comments. So we could essentially just comment on any model. However, we're just going to relate this to a post ID for now. And again, this will be unsigned. So here, what we can do now is say table foreign. And of course, we're relating the post ID to the ID on the posts table. So pretty straightforward.
09:49
And then we want to say, well, on delete, we want to cascade. We can do a similar thing as well with the user ID. So all we need to do is duplicate this down. Of course, that should be posts. But now this can be the users table. So we're relating the post ID here to the ID on the posts table. We're relating the user ID to the ID on the users table. So if a user is deleted, all the comments
10:15
will be deleted. If a user is deleted, all the posts associated with that user will be deleted. And of course, if a post is deleted, now all of the comments associated with that post will be deleted. So really, it's up to you how you want to set things up here. But that should make a lot of sense. Let's just go and run our migrations. And let's take a look at our comments table now.
10:40
So again, let's just very quickly set this up. So I'm going to create a user just in here. And over on my posts, I'm going to set a user ID of two now. And on my comments, I'm going to go ahead and set this as well. So the user ID in this case would be two. And the post in this case will be one, I think. Maybe not. Let's just check.
11:06
That's now five. So let's go and just set this up. So it's two and five. There we go. So it's allowed us to insert it now because obviously, we have them constraints in there. And now what's going to happen when I delete the user ID? Well, that's going to delete the posts and all the comments associated with that. We'll test it
11:24
out with the posts first. So let's just get rid of that post. Now when we go over to comments, there are no comments. And again, if we just create another post here, so let's say user ID of two. The comments here, let's go and set a body in here. Set a user ID of two and a post ID of six, I think that should be now. Perfect. And now what we can do is check what happens
11:46
when we delete this user. Of course, like I said, the posts and the comments will all be deleted like so. So that's cascaded down to delete everything. So pretty straightforward stuff. You can include these new migrations just to enforce referential integrity. And it's very, very nice and easy with Laravel's Schema Builder.

Episode summary

In this episode, we dive into how to maintain referential integrity in your database tables using Laravel's Schema Builder and migrations. That might sound a bit technical, but it's a crucial concept to make sure your data isn't out of sync, like having posts that belong to users that don't exist anymore.

We kick things off by creating migrations for a simple users table and a posts table, so you can see how tables relate to each other in a real app. Then, we see what happens if you don't have any constraints—yeah, it can get messy! Posts might hang around after their user is gone, which can break your app or make your code more complicated than it needs to be.

That's where foreign key constraints come in. Laravel makes it super easy to set them up, so that any post must belong to a valid user. We walk through adding a foreign key to the migration and show how to set the proper data types to avoid errors. Then, we see how to make things even safer by adding onDelete('cascade'), so when a user is deleted, their posts are automatically removed, keeping your data clean.

We repeat the process for a comments table that relates to both users and posts, cascading deletes all the way down. The end result: your app's database stays tidy, you avoid weird bugs, and you hardly have to write any extra code thanks to Laravel's Schema Builder. It's a big win for app stability, and you'll see just how easy it is to set up and test.

Episode discussion

No comments, yet. Be the first!