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

Orders table

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:

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.
• JOINs work if there are Foreign Keys or not.
• Foreign Keys work if you extract data with or without JOINs.

Conclusion

JOINs and Foreign keys do not do the same thing.

JOINs 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 JOINs to build your queries.

Thanks for reading I hope you found this article useful. Let's catch up on twitter.