Welcome back. Now we've gone over a few SQL commands
and you should feel pretty comfortable with most of them. But there's one thing that I've kind of omitted
until now. You remember here when we spoke about relational databases I mentioned something about our
primary key and a foreign key.
How these tables are linked with one another with some values like username.
Well we're going to start introducing this idea of connecting the tables and how we can do that.
The first thing I want to show you is this command right here. I want to create a login table with create
TABLE which we've seen
And now within here,
I have a few things.
I have an ID that has data type serial.
Let's look at what that is.
We see that the data type serial is an auto incrementing integer value.
OK.
And this auto incremented value every time a new user is created.
It'll go one, two, three, four, five.
So it's a unique ID that we can assign to each user.
However, this is for the login table.
So that is we're creating an ID for each login entry here.
And you see that it says NOT NULL.
Not null means that this well has to be a filled in property.
Remember when we had the users table and we didn't have the scores and we had null here.
Well if we do not NOT NULL, that means that, that's not possible.
And then we also have primary key and setting primary keys
when you're creating a table is one of the most important things. By saying that the ID here is the
primary key.
It's saying that this is what I want you to access when I'm looking for things. And databases are really
really good.
When you set a primary key to grab this information. And as you know primary keys are only one per table.
Usually something like an ID or something unique like an email. By setting this as primary key,
we now have a really, really fast way of selecting or grabbing information.
If we look at the second value here, the second column. We have secret which is a variable character
of a 100.
Let's look at what that data type is. For the string datatype variable character has a size,
and we can say that numeric characters to store.
So we're saying that the secret value in our case this will be the hash will not be greater than a 100.
And it also is not null, that is this always has to be filled when we're inserting something into this
table.
Otherwise it's gonna fail. And then we have name which will be text that we've used before.
But it also has to be unique. And the unique Keyword says that if a name already exists well then we
can't insert into it.
So let's run this command and see what happens.
Let's refresh.
We have the login table now with ID, secret and name.
Let's enter some information in here.
I'm going to say INSERT INTO login.
We don't need to now insert anything in the ID because that's actually done for us because it's serial,
it will just automatically increment. But for the secret we can say that secret will be a hash and the name
will be some sort of a text. Let's format this a little bit.
VAULES and we'll say some sort of a string.
We'll just say 'abc' here.
And the name will be 'Andrei'.
let's run the query.
Let's go back to the login, refresh, go to login.
And we see that Andrei has been inserted with ID of 1. If I go back to the query and add a different
person let's do Sally and her password is 'xyz'.
Run the query and now should be, in there.
And you also noticed that her id is now 2. What if I enter this again.
I get an error, duplicate key value violates unique constraint "login_name_key" because we've said that
we want unique for name.
Very cool.
Let's enter the last one.
And this will be
John
because that's what we have in our users table.
So we have 'John' perfect and if you go to login we also have 'John' with his secret which is 'lol'.
And notice here that it's four instead of three because we ran a command but we failed to do this, but
doesn't really matter because as long as these are unique. These are the primary keys.
This is the one thing that we make sure that is unique at all times in our table. But by doing this we've
created something interesting.
We have Andrei, Sally and John that also live with Andrei, Sally and John in the Users table. When we design
databases like this we can now have Andrei, Sally and John referencing the users.
And if I wanted to grab Andrei or Andrei's secret which is 'abc' in that case I have a foreign key which
is this. This name column in the login is the foreign key that references the- in our case the primary
key here because these are the unique identifying values in the Users table.
That is Andrei.
In the next video I'm going to show you how we can finally connect these two together.
I'll see you in that one.
Bye-bye.