Teacher: Hi guys, and welcome back.
Hope you're doing great.
In this video we're going to learn about relational data.
In a bit more depth,
we're going to learn about Primary and Foreign Keys.
And although we've mentioned these a little bit in the past,
but now it's gonna all be much clearer.
So here we've got two tables,
we have the User's table on the left
and the Accounts table on the right.
On the left, we have information about users.
And often we assign each row a ID column,
which is unique to each row.
So the first user with ID one has a name of John Smith,
ID two has a name of Rolf Smith and so on.
On the right, we've got our holder ID column.
As we learned previously,
the holder ID maps to the ID column on the User's table.
Each account also has an ID column,
which uniquely identifies the account itself.
So we can easily see that user with ID one
is mapped to account with ID three.
Normally these unique numbers,
these ID properties that uniquely identify each row,
and that we use to map rows from one table to another,
we call those Primary Keys.
And when you're creating a table,
what you're gonna do is you're gonna use
the Primary Key keywords besides the ID Integer column
to the note that this is the primary key of the table.
So here you can see on the left, for example,
when we create a table of the users,
we say that the first column is ID,
the type is integer,
and it is the Primary Key.
The same thing for the table on the right,
the Accounts table.
The holder ID column on the right though,
is not a Primary Key,
because it's not used to identify these rows.
Instead, it is used as a mapping to a different table.
You can see that the holder ID values match exactly
the Primary Key on the User's table.
That is why it's called a Foreign Key.
And that is how you write it.
There on the bottom right we've got Foreign Key
and then the column holder ID that we already defined above,
references, the User's table, the ID column there.
So this is a little bit of a complicated construct there.
But notice that it does not define a new column,
the column holder ID was defined above,
it just says that this is a Foreign Key,
and that the reference is the User's table, the ID column.
The important part is that when we do this,
most database systems including PostgresSQL,
and SQLlite too if it's set up correctly,
will automatically match these two.
And will say that you can't have invalid data.
So if we have an account with holder ID of five,
but no user with ID of five,
that is an inconsistency in our data.
And like I said,
most database systems are going to complain.
There's two problems with this inconsistency.
An accounted with no user is useless.
So if we have an account with a holder ID of five,
but there's no user to match that,
then that account is essentially orphaned,
and it's not that useful.
But if you create a new user,
and it so happens to have the ID of five,
then that user is automatically gonna map to that account.
And it's gonna inherit that account
even if it wasn't theirs originally,
so that's quite dangerous, as well.
So those are the two main problems we have with this invalid
or inconsistent data.
That's why most database systems
are going to set up these constraints
that prevents you from doing that.
So adding this Foreign Key relationship
is going to enforce some constraints if you use Postgres,
or SQLlite with Foreign Key Support.
So you won't be able to have these inconsistencies.
For example, let's say you have a user with ID five,
and an account with holder ID five,
Postgres, for example, won't allow you
to delete the user unless you first delete the account.
So that's the sort of prevention
that they're gonna do for you.
More information about all of this is in the e-book.
We've got a few examples there and all that.
So do check that out
if you want to read more on the subject.
That's all linked in the resources section of this lecture.
Thank you guys for watching.
I'll see you in the next video.