Hey guys, I am Venkat and in this video, we'll understand what is a composite primary key and
why we need it with a real world example? Before that, let's quickly understand what is a primary
key? A primary key, uniquely identifies a row in a table. You cannot store null or duplicate values
in a primary key column. In the "Books" table "BookId" column is the primary key. Similarly,
in the "Authors" table "AuthorId" is the primary key. So, why do we need this primary key? Well,
to uniquely identify a table row. Even if we have two authors with the same first and last names,
we can uniquely identify them using the "AuthorId" column. In both these examples
primary key contains only one column. In the case of "Books" table, it is the "BookId" column and in
the case of "Authors" it is "AuthorId". So, what is a composite primary key? Well, a primary key
that is made up of two or more columns is called a composite primary key. A common real-world
use case for this is, when you have a many-to-many relationship between two tables, that is when
multiple rows in one table are associated with multiple rows in another table. For example,
there is a many-to-many relationship between Customers and Products tables because a customer
can purchase many products and similarly a product like an iPhone for example, can be purchased
by many customers. Another example is Authors and Books. A single author can write many books and
similarly a given book can be authored by multiple authors. Now, here is the important point to
keep in mind. Relational database systems usually don't allow us to implement a direct many-to-many
relationship between two tables, we need a third table and it is this table that contains the
many-to-many relationship rows. This third table is commonly called link, join or junction table.
In a real world, this junction table would contain only the two foreign key columns and nothing else.
In our example those two columns are "AuthorId" and "BookId". Now, here is the SQL script that
creates all the three tables - Authors, Books and Authors_Books. It is this third table that is our
junction table and it contains the many too many relationship rows and notice in this table, we've
got just those two foreign key columns. "AuthorId" is the foreign key referencing "AuthorId" column
from the "Authors" table. Similarly "BookId" is being referenced from the "Books" table and here
is the code that creates our composite primary key. So, basically we are adding a constraint,
giving the constraint a meaningful name and what type of constraint are we creating?
Primary key constraint. Usually within a primary key constraint we include only one column but
here we're including two columns. So, this is a composite primary key. You can include more than
two columns as well. All we need to do is include another comma and then your third column and then,
if we scroll down a bit, we also have the code to insert test data into these three tables.
In this example, we are creating the composite primary key along with the table that is while
the table is being created, but what if we already have a table and want to create a composite
primary key on that existing table? Well, the following is the code for that. I'll have all
these SQL scripts available on my blog and include the link to it in the description of this video.
A composite primary key is just like a primary key on a single column,
all the rules still apply, doesn't allow null values and cannot contain duplicates
values. An individual column can be duplicated but across the columns they must be unique.
Null values are not allowed in any columns in the composite primary key.
Now, take a look at the existing data we have in "Authors_Books" table. By looking at this data, we
can say author with AuthorId 1 wrote two books and they are the books with ids 1 and 2. Similarly,
the BookId 1 is written by two authors and those authors are the authors with ids 1 and 2. In the
"AuthorId" column the value 1 is duplicated, similarly even in the "BookId" column value 1
is duplicated. So, the important point to keep in mind is, in a composite primary key values in an
individual column can be duplicated but across the columns they must be unique. We already have a row
with AuthorId 1 and BookId 1. Now, what do you think is going to happen if we try to insert a row
with the same values again? Well, we get a primary key constraint violation error. There we go,
violation of primary key constraint, cannot insert duplicate key and the duplicate key value is (1,
1). Null values are also not allowed. If we try to insert a null value in one of the columns or
both the columns we get an error. There we go, all the three statements failed with the same
error - Cannot insert the value NULL. That's it in this video. Thank you for listening.