Foreign Keys

[00:00:00]
>> Okay, so we want to get into some more relational data. So far, we've just been talking about users, which is like that one table. But we're gonna be adding actually technically three more tables to look at, but we're gonna be talking about just two of them. We're gonna be talking about comments from users, and then the boards that those users are posting comments to, right?

[00:00:20]
So that's kind of the hierarchy of our data. We have message boards, on those message boards will be comments and those comments will be posted by users. When I'm talking about a relational database, this is exactly what I'm talking about. This data relates to this one, that relates to this one.

[00:00:35]
So a big key and how you kind of establish these relationships between these various different tables and records in these tables is through a mechanism called foreign keys. When I say foreign, it just means this key refers to something in a foreign table, right a different table. That's what the foreign means in that particular case.

[00:00:56]
They contains data that lives in another table. This first table here, you should recognize, this CREATE TABLE users. By the way, these queries have already been run, you don't have to run this again. This run is part of your big query that you ran previously. But we have this user table this contains all the information about the individual user.

[00:01:15]
We also created a boards table which has a board_id, a board_name, and a board_description. Nothing here looks very surprising. There is nothing new about this that we didn't already learn about users. But comments, this contains some interesting things, let's kind of dig into this a little bit. As a comment_id that's a serial_id, that's more we are aware of that, the GENERATED ALWAYS AS IDENTITY.

[00:01:41]
The second thing that's interesting is a comment is always posted by a user, right? So if you are thinking about a Reddit message, right? A Reddit user posts to Reddit message, those two things are linked, right? They're inexorably linked that you must have a user that posts a comment, every comment must have a user.

[00:01:59]
So this user_id references, and this is an integer right? So integer int, this is just a shorthand for that. This references the users table, and specifically inside of that table, it references the user_id which is this, this thing up here. So this just happens that user_id here, and user_id here, they named the same thing, they don't have to be right?

[00:02:27]
That's just saying here in comments, this is gonna be called user_id. But inside of the users table, we're referencing the user_id. So for example, if this was just called ID up here, not user_id, you would put ID right there. Now it's useful to call them the same thing, we'll see why actually little bit why that is.

[00:02:45]
But you don't have to right, you can call them different things that's totally fine. Same thing with a board_id every comment is posted to a board, right? So you can't just post a comment down into the ether, if you're on Reddit, you can't just post to no separate, every post goes into a subreddit, every comment must go on a board.

[00:03:08]
So that's where this board_id comes from. And it also references board_id from the boards table. And then a comment which is text, right, it's not a varchar. So it's of 65,000 length or less, and then a timestamp of when you posted that comment. Okay, so let's talk about ON DELETE CASCADE.

[00:03:33]
I have a comment, right and let's say user bit hold posted a comment to a board. And then later user bit Holt goes and deletes their account. What should happen to those comments? Because they were posted by user, by default if you don't say anything. So if I left off ON DELETE CASCADE, it would just say, you can't delete this user account until you delete all the comments that they've deleted, right?

[00:03:58]
So I won't let you delete anything if there has existing keys in another table, that can be useful, right? Maybe you wanna do some special kind of cleanup. And you do not wanna delete a user account before you have deleted their comments. This one I I'm saying ON DELETE CASCADE, so whenever a user gets deleted, go into the comments table and delete all of the comments that they've made.

[00:04:22]
This just makes it really easy to cleanup, all I have to do is delete a user account and all those comments just magically dissapear. That's what ON DELETE CASCADE does. And there's also ON DELETE SET NULL. I think that's what it is, yep right there, ON DELETE SET NULL.

[00:04:38]
What this will do, it'll go through on delete the username, it'll just set it to NULL. And, that might be a good thing too, if you wanna keep track of the comments, even if a user deletes their account. So, you kind of decide what your desired behaviors. And, the default action of not allowing you to delete until you've deleted yourself is called ON DELETE NO ACTION.

[00:05:04]
It just means throw an error if they try to delete something from the users table that exists in the comments table.