I'll keep this brief.
We've got a little bit more with primary keys, as we saw in the previous video.
When we define a table, when we create it, we can specify the column that we want to be a primary
key by specifying primary key right after the column name and the data type and other constraints.
But there's another way of doing this, another syntax that you'll see and it looks like this we can
create our column Cat ID, it's an integer, it can't be null.
And then later on we can come back and say, Hey, remember that cat ID make it a primary key.
These will both do the same thing.
It's just a different syntax.
I actually tend to prefer this one here.
However, this one is a little long for slides or horizontally long at least, so I'll often in the
slides resort to this just to keep things a bit more compact so that I can make the text larger.
But you'll see both of them in the world.
You should know that they both exist.
Primary key and then a column name.
In our case, Cat ID is the same as calling it a primary key when we initially set it up.
The next thing I want to talk about is not know.
It is technically redundant to specify a column as a primary key and as not know because primary keys
can never be null.
That's just how they work.
If I copy and paste this one.
Well, it's called Unique Cats.
I already have unique cats, don't I?
Let's drop our table.
Unique cats.
Goodbye and remake it this time.
Cat ID is simply an integer.
It doesn't say not null, but I do make it a primary key.
If I try and insert into unique cats and I do a cat ID, a name and an age, let's just start with a
regular cat that meets those requirements.
Cat ID is one name I'll just go to go this time.
Age is two.
I answered that cat, no problem.
But let's say that I leave off this primary key cat ID.
It doesn't say not, no, but it is a primary key.
That means it's required.
And if I hit enter, well, I get an error this time because I told SQL I was going to provide a cat
ID and I didn't.
So let me fix that.
I'm not going to provide a cat ID as if I was expecting it to allow me to insert it without an ID.
But we have a problem.
It says Cat ID doesn't have a default value and that's the same error we saw when we set up not null,
but we didn't set up, not know we set up a primary key.
If I describe my cat's table though, or my unique cat's table, we do see that it cannot be null.
So I didn't say that.
I just said it was a primary key.
But it's redundant to specify not null, because that primary key constraint requires that value to
be present alongside being unique.
The final thing and most useful thing that I'm going to show you, I think in this video is setting
up auto increment on your primary key fields.
So there's a special thing auto underscore increment.
It's easy to forget the underscore because we have primary space key, but it is auto underscore increment
is going to make a column automatically increment starting at one.
You can change that, but it doesn't really matter for us.
We'll have one and then two and then three and then four and we don't have to go and add anything.
We don't have to manually insert and keep track of the unique IDs.
We can just rely on the auto increment.
So let's go ahead and copy this and paste it in.
And it said also called unique cats.
All right.
This one is unique.
Cats three terrible name.
But if we do that, I can now insert into unique cats.
Three And I don't have to specify a cat ID, I simply need name and age.
And technically, I don't need any of those, actually, because they're not required.
They could be.
No, but let's add them in.
So name will be Boingo and age will be one for this first cat.
Let's take a look.
Select start from unique cats three and we see that cat ID was automatically assigned and it's set to
one.
Now if I insert another cat, I can actually just insert the exact same cat in terms of name and age
and keep doing that.
And every single row that I insert here will have its own unique cat ID because I set up that auto increment.
So from unique cats, three select everything.
They all look the same.
Boingo one.
Boingo one but cat ID automatically incremented for each one of them.
So this is really useful because now I have this primary key that doesn't require any effort on my part
to be unique.
I don't have to keep track of a number and add one or anything like that.
I just insert a value and we automatically get that unique ID, so that's super common to do.
We combine auto increment with primary key all the time and you can do it on the same line.
Just want to be clear about that.
If I copied this, let's say I'm doing it from a script like over here in a file, I can do this auto
increment primary key.
It doesn't have to be on separate lines and let's call this unique cat forward, which is annoying,
but let's just call it that and run this script.
I've got an extra comma here, so it's going to complain to me, make sure I'm in the pet shop database.
All right.
And how do I run u here?
I need to click Lightning Bolt.
Let's describe a unique Cat four and you'll see that we have that primary key.
We have auto increment set up.
And if I were to insert a cat insert into unique cat.
For.
Values.
Let's say I'm inserting nothing.
It's just going to be empty.
I'll do it a couple of times.
And then I select everything from my unique cats for.
We see that ID did work the primary key.
It automatically increments 1 to 3 and so on.
And the last thing I should mention is when you set up the auto increment, if I did try and insert
a cat that manually had a cat ID that was already in use, like insert in two unique cats for let's
say I'm adding the cat ID myself, which you normally wouldn't do if you have auto increment set up.
But if I wanted to add a cat that has the idea of three, I'm going to get an error because there's
already a cat in there with cat ID of three.
Just because we have auto increment doesn't mean that the regular sort of primary key constraint goes
away.
It still has to be unique, even if I'm not the one usually making this number right.
It's I'm relying on the auto increment capability.
If I try to insert a duplicate value, I'll get an error and it won't insert.
So that's primary key and auto increment.
You'll see them together all the time.