In this article we'll introduce the concept of foreign keys in a database.
Let's look at an example in order to understand this concept.
In previous articles we played with
SQL queries using something called "W3 Schools SQL Try It" tool, that we can find here.
This tool provides us with a database and several tables, like
Categories, and so on, so we can try out different
SQL queries and learn how to select and display records.
Let's select all records from the table
SELECT * FROM Products;
This query brings up a bunch of Product records with several attributes, organized in columns:
The first column, labeled
ProductID, represents the primary key of this table. As we have seen before, the role of the primary key is to identify uniquely each record in a table. A primary key is typically a unique integer but it can be any value, as long as it's unique.
Products table, we also notice some descriptive columns like
Price, specific to each product.
A couple of other columns are different, though.
CategoryId only contain an integer, and as we can guess, they somehow refer to records inside other tables:
ID fields in the
Products table, that refer to other tables are called foreign keys, because they refer to a separate (or foreign) entity.
The role of foreign keys
But why do we even refer to other tables for this kind of information? After all, a supplier is specific to a product and its information could easily be stored inside the
Products table, in a
Well, if each product had only one supplier, it would probably make sense to keep supplier data in one table. But typically, in the real world, several products may come from the same supplier, and we would have to enter the same supplier data in several records inside the
This means we would have the same, repeated information throughout our database table. This is not a good practice. If our supplier changed address, we would have to update the address information in several places in our table, and this would not be very efficient if there are thousands of records.
By creating a separate table for
Suppliers (and product
Categories), we can have a better separation of concerns. The
Suppliers table can have all the data it needs and can change it at any time. The
Products table, on the other hand, is only concerned with the
SupplierID to find the supplier for its products, no matter what data the supplier keeps.
Finding and displaying connected data
We have just seen that keeping data in separate but related tables has its advantages. But, how do we access data in two different tables?
Let's say we want to display a list of products showing the supplier name next to each product. How can we find the right supplier for each product?
This is where the concept of a multi-table query comes into play. We can collect data from different tables with a
SQL statement called
JOIN. Here's how we use it.
We know, by looking at our database schema, that the
Products table has a column called
ProductName and the
Suppliers table has a column called
We also know that
Products has a
SupplierID column, that acts as a foreign key to join
Based on this knowledge, We can write a statement that joins these two tables by the
SupplierID column, with this syntax:
SELECT ProductName, SupplierName FROM Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID;
Let's take a close look at what happens in the query above.
We first select the two columns we want displayed:
SupplierName. Since these columns belong to two different tables we must specify which tables they come from. They come from
Suppliers, so we join the two tables, with the
But we can't stop here. We also need to specify the conditions of the join, that is, we must tell
SQL which column to use to join the tables.
Since we know that both table have a
SupplierId column that contains the same key, we specify this condition after the
ON statement on the last line.
Products.SupplierID means the
SupplierID column of the
Products table. In the same way,
Suppliers.SupplierID means the
SupplierID column of the
The resulting SQL statement describes exactly which columns we want, which tables we need to connect (or join) and which columns in each table we want to use as the basis of the join.
These are the records pulled up by the query:
And with this, we have seen how to use JOIN to connect two tables based on a foreign key. We will see more options in future articles.
Did you like this article? Share it with your friends.
I write daily about front-end and back-end web technologies.
You can receive all my articles in your inbox by subscribing to my newsletter. Just click the button below. No spam, just good, useful content. Guaranteed!