Narrator: Hi guys, and welcome back.
In this video, we're going to learn
about SQLite vs. PostgreSQL,
and how they are different.
The first difference is in database size
and the setup time.
SQLite is very easy to set up and use,
the database is a small, single file
as we've seen.
For example, with a file called data.db.
But, PostgreSQL is quite large in size,
and it can require a lengthy setup.
A PostgreSQL database has a server accepting
incoming connections and responding to them,
and, you know, it is quite large,
and can be difficult to set up.
SQLite is pretty easy to set up,
and it's normally a small file.
This directly affects the speed.
Only one SQLite connection can write to
the database at once.
That is because it's in a single file,
and you can only have one thing changing
a file at a time.
So therefore, when you're connecting to SQLite
from say, multiple different applications
to a single SQLite database,
only one of those can change the file at a time.
If two of them want to change the file,
one of them has to wait until the other one's done.
So, SQLite is great for reading
because when you're reading you don't have this limitation,
but not so great for writing.
PostgreSQL doesn't have this limitation,
so it's good for both.
We've also got the issue of primary and foreign keys.
We've already discussed this.
PostgreSQL forces us to use foreign key constraints.
So, SQLite has to have this enabled,
and you can disable it.
For example, with users and accounts.
Let's say we've got an accounts table
with a foreign key of user_id,
and you've got a user with a primary key.
As we've learned already,
you can't delete a user that owns an account
without first deleting the account.
That is because if you delete the user
and you leave the account,
when you create a new user, if it has the same ID,
it would inherit the account.
In PostgreSQL though, we've got CASCADE constraint.
With a CASCADE constraint, we can tell PostgreSQL
what to do with an account when we delete
the corresponding user.
So, for example,
here we've got some code that creates a table,
and let's say that we create this accounts table,
with a user ID and account number.
It's got a foreign key of user ID
that references the users table.
And, we have ON DELETE CASCADE.
So, let's take a look at what ON DELETE CASCADE does,
as well as some other options that we've got available.
So, we've got here ON DELETE CASCADE,
and this diagram here represents
the relationship between user and account.
So, what we're saying here
is that one user can have many accounts.
The box on the left represents the users table,
or user row,
and the account on the right represents
the accounts table, or multiple account rows.
So, then we've got ON DELETE RESTRICT.
This is a different option
that we have available in PostgreSQL,
and we also have the default,
which is ON DELETE NO ACTION.
So, when we create a foreign key
with the ON DELETE CASCADE constraint,
as we showed just a moment ago,
what happens when we delete the user that has an account,
is this.
Essentially, it cascades.
The deletion cascades through both tables,
and you delete the account as well.
If there's many accounts for that user,
they all get deleted.
With ON DELETE RESTRICT,
if you try to delete the user,
then it'll just come back.
It won't let you delete it if it has an account.
Notice if there are no accounts for the user,
then the deletion would still work.
Finally, the default,
which is something that we've seen already,
is that when you try to delete the user,
you then get an error,
if there are accounts for it,
and it tells you, you can't delete this user
because it has accounts related to it,
or basically tells you that the foreign key constraint
would be violated if you delete the user.
So, these are the three things that we can do
with on delete.
And, just as a recap,
you can delete the users account freely.
So, if you have a user and one account,
you can delete the account.
And, that's totally fine,
that doesn't violate any constraints.
But, you can't delete the user that has an account,
unless you delete the account first, of course,
or you use ON DELETE CASCADE.
If you use ON DELETE RESTRICT,
you can't delete the user, it will just come back.
And, if you use ON DELETE NO ACTION,
which is the default,
then that would raise an error.
All right, thank you guys for watching this video.
I hope you've learned something,
and I'll see you in the next one.