Foreign Keys & Managing References

[00:00:00]
>> Let's talk about foreign keys. So we have our recipes table. What happens if we delete from it? So this is the khachapuri. If we delete that, we still have photos in the other table. That's a problem, we don't want orphan photos. So let's just run that. Okay, and now we'll select that.

[00:00:35]
We still have photos here, right? That's a problem. We don't wanna have photos just hanging out in our database if they don't correspond to a recipe. Wouldn't be good if we could constrain that if we delete something from our recipes' table, that necessarily those things have to go out of the photos table?.

[00:00:59]
Well, lo and behold, there's something called a foreign key that actually will do that for you. So I'm actually going to reset my table right now cuz I wanna show you everything works the same way. So if run this little query right here, it will just reset your table to everything being a good data again.

[00:01:23]
This is kind of an interesting one here, the IF EXISTS. So if it didn't exist, this wouldn't do anything. So this is just as like a bit if resiliency to say hey, if this exist drop it. If not, do nothing. Okay, so we did that. Now we're gonna recreate our photo's table a little bit different thing time.

[00:01:46]
So CREATE TABLE recipes_photos, okay? We're gonna give it a photo_id of integer. We can also just give it ID, either is fine. INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, okay? We're gonna have a URL which is a VARCHAR(255) NOT NULL. And then we're going to do this recipe_id, but we're gonna make this one a little bit different.

[00:02:26]
It's gonna be an INT but it's gonna say REFERENCES recipes, the recipes table and specifically the recipe_id in that table. Then you have to tell it what to do if a delete happens. So you could do ON DELETE. There's like do nothing I think. There's set null, so it's not do nothing.

[00:02:50]
It's no action which basically means hey, if someone's trying to delete out on this table and has not already cleaned up the other table, just throw an error. Make them go clean the other thing first before you come back to me. Now I'm lazy because if someone deletes something from the recipes table, I just wanna delete those photos, right?

[00:03:11]
So I'm gonna say ON DELETE CASCADE. This is basically saying hey, if someone actually deletes out of the recipes table, just delete everything that this references in the other table. So now if I run this DELETE that I ran up here, what would you expect to happen to its photos in the other table?

[00:03:38]
They would go, right? They would delete. The delete would cascade to the recipes photos tables. If I put no action, then what it would do is this would error until I ran the delete on the other table first. And then if I did set null, what I would do is it would just set recipe_id to be null, right?

[00:04:02]
So then I would have orphan photos but they don't have any recipe IDs they do hook up with. So I just showed you here ON DELETE. You can also put on an on update if there's some sort of like synced state between the two. Let's say I have like, I don't know I'm making stuff up now but let's say I have like English photos and French photos.

[00:04:23]
And if I update a photo to be French instead of English and you want that update to cascade to the other one, you can handle that with the on update part of it. We're not gonna do anything with that today. We're just care about the ON DELETE. But that's what's cool about a foreign key is now I can have this tight relationship between the table A and table B.

[00:04:46]
What's also cool about this if I try and INSERT INTO recipes_photos VALUES, I guess have to put here {recipe_id, title, body} then I put VALUES, some number that's not realistic,' thing', 'other thing'. Did I not create that already? Did I call it recipes_photos? Yeah. The recipes_photos, and it's URL.

[00:05:48]
Not title body, photo_id, recipe_id, So I just need a URL. Anyway, Now, what it's gonna tell me here, this violates the foreign key constraint which basically said hey, you're trying to put something into the photos table. That's trying to reference something in the recipes table, but that doesn't exist.

[00:06:17]
I'm not going to let you insert this until you've fixed that. So now I can accidentally insert orphan photos as well. So that's what this foreign key constraint does, is it makes sure that these things stay in sync.