OK 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 a name or age.
What does this mean.
So to dive into this and this is actually something we're going to come back to quite a ways down the
line in this course.
But we'll experience working with Keith for the first time in this video.
So to illustrate the problem right now I could do this I could insert identical data Montie 10 Montie
10 Montie 10 Montie 10 and so on.
And we've already seen this.
But just to prove that to you if I just worked with cats let's do select start 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 cat's name age values and this one will be called Helena
and I will be six or Halina 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 and 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 Tilla 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 has no age no.
And then we have know who is age.
No there is a way of telling these apart even though they both have age of gnoll I could tell sequel
and we don't know how to do this yet but I could say give me a cat whose age is nil and whose name is
Alabama and that can get me this.
But there's no way for me to get this cat separated from this cat or this one and separated 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 user name.
There's 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 cult 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 cold and then another cold 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 typed
my username and it's going to go to the database and try and find a user with username cult and then
it's going to match my password.
But if there's two users with 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're 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 I.D. a unique
identifier so you can see here it added a new field cat I.D. and every cat is given one of these IDs.
So now rather than saying I want to select Montie from this database and getting all of these I can
say I want the cat with I.D. one or ID three.
And they are uniquely identifiable.
All right.
So there is 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.
The primary key just means that whatever field we assign whatever we make the primary key.
So in this case we had made 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 you need cats we have name and age but we also have cat I.D. now.
So Cat I.D. is an integer and we can't have cat Id know.
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 to be unique.
We want it to be the unique identifier so we just pass it in here cat.
So let's try this up.
I'm just going to copy it over just to save you time.
You don't have to watch me type thing.
Here we go.
Create table cats.
Cat ID name age.
And by the way that cat Id talked about earlier is primary key.
All right so now let's do describe unique cats you'll see a couple of things.
We have cat deal now because we had this line here but we also under this key call them we have primary
here 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 will 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 and age will be one that's used to
find 23.
Except the typo.
Oh get it 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.
OK.
Now just double check our work.
From unique cats get 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 two 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 entered into unique cats.
Cat ID name age.
What if I tried to make another one with of one and this one is called James.
Just like that I get a new error duplicate entry one for key primary.
So this is saying wait a second you said as 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 added 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 user name a primary key so that you can't have more than one account with the
same username in your database.
And so all that you would do is instead of saying Primary Key Where's that cat ID you would say a primary
key username or whatever the name of the field is.
And then if I tried to insert something with an already existing user name I would get the same error
here.
OK.
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 you just supposed to know where you left off one two three and every time you enter 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 and it's just a pain.
So there's a way around it.
Our code gets longer.
I know that all of 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 1 to it every time a new cat is created.
So we no longer have to specify cat ID is one cat ideas to it will just do it we just leave off Id entirely
and everything else here is the same.
So Cat unique cats too.
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 cats to notice that we also have auto Inc.
We have autoincrement here under extra so it doesn't get its own field but is considered extra.
So now let's try and insert something into that insert into unique caps too.
And will you 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 the 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 ID is automatically one.
And to prove to you that it's auto incrementing.
Let's change this name to Jeff
and we like start 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 Jeff and another Jeff again and this time let's do it skippy as well.
Now select star from unique cat 2 and you could see we have Skippy here who's for it skippy here has
four but they have different ideas.
Therefore they are unique.
Same thing with three fifths perfect already.
So that concludes all the new stuff in this section.
Now you're going to get a chance to put in 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.
OK.