-: Hi guys, and welcome back.
In this video we're going to learn
about autoincrementing IDs.
Why do we want to use autoincrementing IDs
and what are they?
Well, we often want a way to refer to
a specific row in a table.
We've seen this with the ID columns
in the tables that we saw earlier on.
An autoincrementing column means that every time
we create a new row in the table,
a new value is created for us
that we don't have to specify ourselves.
So we don't have to think about it.
This is useful because, for example,
if you create a new movie,
you maybe don't wanna check how many
other movies you've got in the table,
so that you can come up with a new unique ID.
So the autoincrementing IDs can be pretty useful for that.
So how do we use them?
Well, when we create a column that is an
INTEGER PRIMARY KEY in SQLite,
that will automatically be autoincrementing.
In PostgreSQL, we're gonna have to specify
this behaviour ourselves, but it's very easy,
just a different type of data that we'll use there.
So, why are primary keys in SQLite
autoincrementing by default?
Well, it's because of a couple of things
that happen behind the scenes.
All SQLite tables have a hidden column called rowid,
and that is an autoincrementing column.
So when we create an INTEGER PRIMARY KEY column,
that new column becomes an alias for rowid.
So it takes the same value essentially.
Therefore, there's no performance loss
by creating an INTEGER PRIMARY KEY column,
because it just uses the same value
that was already generated anyway.
Let's learn more about this rowid column.
Something interesting to note
is that SQLite may reuse rowid values.
So, like we saw earlier,
reusing primary key values can be dangerous.
For example, if you have users and accounts,
and you delete a user but you don't delete the account,
then you create a new user,
and it takes the same value as the old one,
the account now becomes associated with the new user.
We saw this earlier on in another presentation.
And so that can be dangerous, right?
In PostgreSQL, autoincrementing values,
as we will learn later on, only ever go up.
So it will never reuse old values,
but SQLite might reuse old deleted values,
although it normally doesn't.
So it just depends on how large your IDs get
that it might end up reusing old ones.
It normally doesn't though, so it's not a big concern,
but it's just something to be aware of
because in SQLite we have
the AUTOINCREMENT keyword as well that you may encounter
when you are learning more about SQLite.
And you can define a column as
INTEGER PRIMARY KEY AUTOINCREMENT in SQLite.
You can do that, and that means that SQLite
will only ever increase the rowid
and will never reuse old numbers,
but this requires much more computing power
due to the algorithm and how it's implemented in SQLite.
So if you are concerned about using rowid values,
and primary key values, because they are getting very large,
and we're talking thousands of millions in terms of numbers,
then do look into these.
Read the reference that we've got in the resources section
of this lecture for more information,
but you normally don't have to worry about that.
The main purpose for me showing you the
AUTOINCREMENT keyword in this lecture is precisely to
tell you that you don't need it most of the time.
So don't worry about it.
You don't need the AUTOINCREMENT keyword.
Just using the primary key will
automatically give you an autoincrementing value,
and that is an alias for the rowid column.
All right, thank you guys for watching.
I'll see you in the next video.