Okay.
As you can see, the slide says we have one more thing.
And that is true.
We have one more thing to cover here before we do our final exercise in this section.
So in keeping with this pattern, what's up with this this key column here and it's empty.
There's nothing for name or age.
What does this mean?
So to dive into this and this is actually something we are going to come back to quite a ways down the
line in this course, but we'll experience working with Key for the first time in this video.
So to illustrate the problem right now, I could do this.
I could insert identical data.
Manti Te'o, Manti Te'o, Manti Te'o, Manti Te'o and so on.
And we've already seen this.
But just to prove that to you, if I just work with cats, let's do select star from Cats.
And this is true about any of the cats tables we have.
You can actually already see we have two cats that are identical.
No and no.
But I could also do something like insert into cats name, age, values.
And this one will be called Helena.
And Helena will be six or Helena, however you say it.
We can do this and we can do it as many times as you want.
And there's going to be identical data in there.
As you can see, we have five identical cats.
So why should you care?
Why is this really a problem?
And there's a couple of reasons, but it comes down to the fact that we want all of our data to be uniquely
identifiable, even if it looks the same like it does in this case.
We assume that these are separate cats.
If they are in fact one cat, then this is just an error.
We don't want to have this many entries, but imagine we actually have five cats named Elena with an
age of six.
They are unique and individual and we want to be able to keep track of them.
And right now we can't.
There is no way to uniquely refer to this.
So in a case like here where we have Alabama, who is null, age null and then we have null, who is
age null, there is a way of telling these apart even though they both have an age of null.
I could tell SQL and we don't know how to do this yet, but I could say Give me a cat whose age is null
and whose name is Alabama, and that can get me this.
But there's no way for me to get this cat and separate it from this cat or this one and separate it
from this one.
And that's really a useful and important thing.
And it's actually a really fundamental part of working with databases, is having things be identifiable
when you're signing up for an account on, let's say, Twitter and you're trying to make a username,
there is a reason that you can't just make the same username that already exists.
I wish that I could just go and make my username Colt, but somebody already has that.
And it's not just about keeping it simple and not being confusing for users, because obviously it would
be confusing if there was one colt and then another colt and they're both tweeting, how do you know
who is who?
But actually in the code, that's hugely problematic because there's things like when I log in, I type
my username and it's going to go to the database and try and find a user with username colt and then
it's going to match my password.
But if there's two users with the username Colt, then what does it do?
So in that case, you're not allowed to have separate usernames.
But there are times when we have data that looks the same.
Like hypothetically, we could have two cats named Helena, age six, or maybe a better example.
We could have a couple of cats if we go back.
Here where we have no name provided they're unnamed, but they are unique, different cats.
How do we know?
And the easiest way and what we'll do and what pretty much everyone does is assign an ID, a unique
identifier.
So you can see here I added a new field cat ID and every cat is given one of these IDs.
So now, rather than saying I want to select Monty from this database and getting all of these, I can
say I want the cat with ID one or ID three and they are uniquely identifiable.
All right.
So there's a precise term for this primary key.
So a primary key just refers to something that is a unique identifier on a row.
It's a way of identifying something.
And you know that it will be unique.
Just a heads up.
We will see another type of key later on called foreign key.
Those are both keys.
Don't worry about it.
But primary key just means that whatever field we assign, whatever we make the primary key.
So in this case, we would make cat ID.
A primary key means that it has to be unique.
So let's see how we actually assign one.
So I'm creating a new table here, and I know it's a little bit longer than what we've seen, but a
lot of it is very, very similar.
So we have unique cats.
It's just the name of the table.
Just I got tired of cats.
One, two, three, four.
So unique cats.
We have name and age, but we also have cat ID now.
So cat ID is an integer and we can't have cat ID B?
No, we definitely want there to be something there.
And then this is the new and important piece.
This is just another field.
This isn't technically new conceptually, but this is primary key.
And then we give it the name of a field.
So we want the cat ID to be unique.
We want it to be the unique identifier, so we just pass it in here.
Cat ID.
So let's try this out.
I'm just going to copy it over just to save you time so you don't have to watch me type the whole thing.
Here we go.
Create table unique cats, a cat ID name age.
And by the way, that cat ID I talked about earlier is a primary key.
All right, so now let's do describe unique cats.
And you'll see a couple of things.
We have cat ID now because we have this line here, but we also under this key column, we have primary
or PRI, but it means primary key.
So cat ID is a primary key.
So how do we know?
Well, let's insert into cats or unique cats.
We'll do ID, name and age just like that.
And then we'll do values and we'll start off with an idea of one name will be Fred.
An age will be one.
Let's do two.
Fine.
23.
Except the typo OC hit enter.
Oh, we have a problem.
I just referred to ID.
It should be cat ID.
Silly mistake.
So let's fix that cat ID.
So now just double check our work.
From Unique Cats.
Cat ideas one.
All right.
So we could do the same thing.
Change us to two.
And instead of Fred, let's have Louise.
Who is to.
Three we enter.
Same thing.
I think you get where I'm going here.
We can keep doing this, however, and this is where the primary key part comes in.
I can now say insert into unique cats cat id name age.
What if I tried to make another one with ID one?
And this one is called.
James.
Just like that.
I get a new error duplicate entry one four key primary.
So this is saying, wait a second, you said it's a primary key.
I cannot have a duplicate entry.
You cannot have something.
The whole point is that ID is supposed to be uniquely identifiable.
Well, that's a problem.
So it doesn't add it to the database.
So that's really, really important.
And IDs are not the only thing you can make.
Primary keys.
I gave that example of username, which is something we'll come back to later.
You often want to make a username, a primary key, so that you can't have more than one account with
the same username and your database.
And so all that you would do is instead of saying primary key, where is that cat ID?
You would say primary key username or whatever the name of the field is.
And then if I tried to insert something with an already existing username, I would get the same error
here.
Okay, one more thing to talk about, which is it's kind of annoying to have to manually specify this
id.
It's more than annoying.
It's impractical.
I mean, are you just supposed to know where you left off?
One, two, three.
And every time you insert a cat, you just have to know what the last one was.
It just doesn't really work.
It's not even in this shell context, but especially not in a real application.
You can't.
It's just a pain.
So there's a way around it.
Our code gets longer.
I know, but all that's added here is this auto increment.
So it does what it sounds like.
It will take cat ID and it will automatically add one to it every time a new cat is created.
So we no longer have to specify cat ID is one, cat ID is two.
It will just do it.
We just leave off cat ID entirely and everything else here is the same.
So cat unique.
Cat two I'm just going to copy this over, make some space hit enter and before I show you that it works.
If we do describe Unique Cat two, you'll notice that we also have auto whoops.
We have auto increment here under extra so it doesn't get its own field, but it's considered extra.
So now let's try and insert something into that insert into unique cat two and we'll do name and age.
We don't specify cat ID anymore values.
And let's see, seriously, the hardest part of all of this.
Let's go with Skippy.
Once again, I see some peanut butter in my kitchen and age will be four.
Just like that.
Now if I do select star from unique cats to cat, it is automatically one.
And to prove to you that its auto incrementing lets change this name to Jif.
And we select Star again.
And now we have two.
So this solves two problems.
One is that it's annoying to have to manually type those IDs and keep track of what number we're on
next.
But also, I can now have duplicate cats as far as name and age, which is our whole goal from the get
go.
So I can now do another Jif and another Jif again.
And this time let's do a Skippy as well.
Now select star from Unique Cats two.
And you can see we have Skippy here who's four and Skippy here is four.
But they have different IDs.
Therefore they are unique.
Same thing with three gifs.
Perfect.
Alrighty.
So that concludes all the new stuff in this section.
Now you're going to get a chance to put an action to actually practice it and to write your own bit
of code.
Create your own table that looks a little bit something like this.
Okay.