Overview: In this post, I explore joins and foreign keys by what they do and how they work independently. I also show how these two work hand in hand to ensure data integrity when using joins.
We'll refer to our sample database tables: customers
and orders
for demonstration.
Customers table
ID | Name | City | Age |
---|---|---|---|
1 | John | New York | 35 |
2 | Agnes | Los Angeles | 23 |
3 | Raphael | Houston | 41 |
4 | Gustavo | Nairobi | 50 |
5 | Brenda | Philadelphia | 26 |
6 | Whitney | Chicago | 18 |
Orders table
ID | Name | CustomerID | Amount |
---|---|---|---|
1 | Box | 5 | 3500 |
2 | Iron Box | 1 | 2300 |
3 | Television | 4 | 1200 |
4 | Play Station | 3 | 6100 |
5 | Computer | 6 | 1800 |
6 | Oven | 2 | 2000 |
According to our sample tables shown above, rather than storing the customer details in both tables, we store CustomerID
on our Orders Table that shall contain values existing in ID
column of Customers Table. This technique is known as normalization. It helps reduce duplication of data.
What is a JOIN
?
SQL has the ability to combine data from two or more tables based on a related column between them.
We can achieve this using a JOIN
.
A JOIN
is used to combine rows from two or more tables, based on a related column between them.
It is used when retrieving data from the DB.
Using JOIN
We shall join our sample tables to be able to get details of order made by a customer. The two tables are related via the CustomerID
column, which holds values that are present in ID
column of Customers Table.
SELECT customers.ID, customers.Name AS customerName, orders.Name AS productName, orders.Amount FROM customers JOIN orders ON customers.ID=orders.CustomerID;
When we query our database, we get the following results:
ID | customerName | productName | Amount |
---|---|---|---|
5 | Brenda | Box | 3500 |
1 | John | Iron Box | 2300 |
4 | Gustavo | Television | 1200 |
3 | Raphael | Play Station | 6100 |
6 | Whitney | Computer | 1800 |
2 | Agnes | Oven | 2000 |
The results we have is data joined from two tables.
However, We have a lurking problem.
When we add new records to our orders
table, we can add new values for the CustomerID
column that do not exist in ID
column of the customers
table.
We do not have any checking to ensure that orders
table only accepts new values for the CustomerID
column when the provided value actually exists in IDcolumn
of customers
table.
We do not want to have order entries created in the orders
table for customers that do not exist.
We can enforce referential integrity for the CustomerID
column in the involved tables using Foreign Key constraint.
What is a FOREIGN KEY
?
Foreign keys are the columns of a table that point to columns of another table that must be a primary key or have a unique constraint.
The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity.
In our case, we need to make CustomerID
column of the orders
table to be a foreign key that references to the ID
column of the customers
table.
Using FOREIGN KEY
We could add foreign key constraint to our orders
table during creation, like:
CREATE TABLE orders (
ID INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
customerID INT,
amount INT,
CONSTRAINT fk_ref_customers FOREIGN KEY (customerID) REFERENCES customers(ID)
) ENGINE=INNODB;
We already have our orders
sample table. Therefore we remain with an option of adding Foreign Key to our existing table. We will use the ALTER TABLE
syntax.
ALTER TABLE orders
ADD CONSTRAINT fk_ref_customers
FOREIGN KEY(customerID) REFERENCES customers(ID);
Just like that...And we have enforced referential integrity between our related tables. The CustomerID
column of the orders
table is now a Foreign Key that references to the ID
column of the customers
table.
A new value in the CustomerID
column of the orders
table must therefore be existing in the ID
column of the customers
table. customers
table can continue having new records but the orders
table is constrained to having/adding data that exists in customers
table for the CustomerID
column.
So, What are the notable differences?
- A Foreign keys enforce data integrity, making sure the data confirms to some rules when it is added to the DB.
- A
JOIN
is used when you retrieve/query data from the DB by giving rules on how to select the data. -
JOIN
s work if there are Foreign Keys or not. -
Foreign Keys work if you extract data with or without
JOIN
s.
Conclusion
JOIN
s and Foreign keys do not do the same thing.
JOIN
s are a way to retrieve data from multiple tables and represent them as a single table.
Foreign key is a constraint to enforce referential integrity. You will still need to use JOIN
s to build your queries.
Thanks for reading I hope you found this article useful. Let's catch up on twitter.
Top comments (0)