Jose: Hi guys and welcome back.
In this video, we're going to talk
about composite primary keys or how to make two columns
the primary key, as opposed to one column
or more than two columns, if you want.
We know that primary keys are primarily used
for linking tables with foreign keys.
But what we haven't explicitly said
is that when you set a primary key,
it also adds an index to the column.
We've learned what indexes are,
so when you create a primary key, that also creates an index
for that column.
And primary keys have to be unique
so creating a primary key also adds
a unique constraint to the column.
Composite primary keys work in the same way,
but instead of the single column being treated
for all of those things,
it is the combination of two columns.
So a composite primary key can have two or more columns
and the columns get a composite index
and they also get a composite unique constraint
where the individual columns no longer have to be unique,
but the combination of columns have to be unique.
Let's take a look at an example.
Here we've got a table where we have the account number,
the sort code and the username.
If you're not familiar with UK banking accounts,
then this is how you came banking accounts work.
You have the account number and the sort code
which is the branch number equivalent.
Then in this table, we have the primary key
as the combination of account number and sort code.
Let's take a look at some sample data.
Here you can see we've got five users
and three of them have the same account number,
but they have a different sort code.
This would be perfectly valid,
even though normally if we have the account number only
as a primary key, this would be invalid
because the primary key is the combination of columns,
none of them are duplicates.
They are all different in combination.
So if we do the same thing
but now we do have duplicate combination of columns,
this is no longer okay, this violates the unique constraint
for the composite primary key.
At this point, I'll also say that
you can apply unique constraints to any column,
not just primary keys.
And when you do that, you can apply them to single columns
or to composite columns as well.
When we're using composite foreign keys, it's very simple.
Just refer to both columns instead of one
using the usual comma separated column nomenclature,
such as we see here.
At the bottom, we've got foreign key
and then inside of brackets, instead of the single account,
we put two accounts with a comma in the middle
and then references the accounts table
and again, comma separated column names.
We can simplify foreign key constraints, by the way.
If the parent and the child table use the same name columns,
we can simplify it to something like this.
Foreign key of account number
and sort code references the accounts table
and Postgres is smart enough to understand
that you refer to the account number and sort code columns
in the accounts table.
All right, that's everything for this video.
Just wanted to tell you
that you can use composite primary keys and what they mean.
Thank you guys for watching, I'll see you in the next one.