The really great thing about databases is their structure. They keep all our data logged logically, making information retrieval and management easier — hooray!
A single table in a database can help somebody run an entire aspect of their business, which is amazing, but here’s the thing… sometimes, it’s just not efficient to use a single table for a single application, and that’s where Foreign Keys come into play.
A Foreign Key, alongside their partner Primary Key, reduces redundant data produced by individual tables by splitting the data between two tables and establishing a relationship or “dependency” between certain attributes. Confused? I really don’t blame you. Let’s discuss the matter in more detail.
Foreign And Primary Keys: An Example
The first thing to understand about Foreign Keys is that they’re never lone wolves, and they almost never partner with each other. In nearly every circumstance, Foreign Keys work in conjunction with a Primary Key, and I’ll explain the difference as we move through the following example.
Let’s say that we’re running a business, and we’re using a single table called Orders in our database to keep track of, you’ve guessed it… orders. Perhaps the columns in our table include Order ID, Customer Name, Customer Address, and Order(s).
If one of our first-time customers really likes our product, they may want to order the same thing again, maybe with a few more things in the basket to try out too.
In our table, we’re going to start developing duplicate or “redundant” data in a few of our columns, namely, the Customer Name and Customer Address columns, and this… this can be a problem.
Problems With Redundant Data
Now, you may not be too bothered about redundant data, as, at the end of the day, all the key information you need to process orders is there in your individual table, but if we really wanted to streamline our operation, our first port of call would be to limit redundancy, and here’s why…
Redundant Data Is A Waste Of Space And Memory
The idea of a database is to record information in the most accessible and efficient way possible, but with a bunch of redundant data creating monoliths of every single table, retrieving information can be a nightmare, even when using SQL queries.
Redundant Data Can Lead To Operational Mishaps
Okay, so let’s say our customer, Joe Bloggs, puts in a number of orders with us at different times, but at a certain point, he moved house, and when we’re scanning our order table, we see his name next to one of his older addresses and mistake it for his current one. We’re then going to send out Joe’s order to the wrong place — sorry, Joe.
If we’re being very careful and filing through our table with a fine-tooth comb, we could of course avoid such calamity, but then the database is making us work for it, rather than the other way round, which defies the object of its existence.
To completely eliminate the chances of human error during data retrieval and product dispatch, we’d have to set up two tables linked by Foreign and Primary Keys.
The Difference Between Foreign And Primary Keys
Now that we’re taking our individual flat-file and turning it into a relational database by introducing a secondary table, we can refine the information in our initial table. Instead of hosting all the records mentioned earlier, it only needs to have Order ID, Customer ID, and Order(s) columns.
The reason being, we’re going to put the other stuff in our second table, which we’ll call Customers. Our Customers table will also have a Customer ID column, as well as a Customer Name column and a Customer Address column.
The Customer ID column in our second table will be our Primary Key. Why, you ask? Well, this is because it uniquely identifies all other records in the table. In other words, the Customer ID data determines the Customer Name and Customer Address data.
But then… if that’s the Primary Key, where does the Foreign Key come in? Finding it is much easier than you might think. The Foreign Key will always be the file in the opposing table with the same or similar title, which, in this scenario, is Customer ID.
Customer ID in our first table does not determine the data in the Order ID and Order columns, but as it’s related to the Primary Key, where the field “originates”, it’s still considered a Key… a Foreign Key.
To save confusion during your further reading on this topic, as the Foreign Key is referencing the Primary Key, it may also be referred to as the referencing key, while the primary key may be referred to as the referenced key.
Furthermore, the table holding the Foreign Key is known as the Child Table, while the other, holding the Primary Key, is known as the Parent Table.
How Does The Connection Between These Keys Streamline Our Database?
Before, when we only had one table with all order and customer information, we had multiple duplicates, which was confusing our operations and wasting database space. Now we have two tables, the information is split into more digestible sections, but that’s not the main advantage here.
The Foreign Key always references, and therefore, guides us to, a unique entry in the Parent Table, meaning we will never accidentally read the wrong information about our customer name or address. The link between these keys guarantees data integrity!
Consequently, all our customers will receive their orders in a timely manner, and we won’t have to pay a fortune in postage to have things returned to our business then resent to the correct location. And, as a bonus, our database is looking slick!
“Dropping” Tables With Foreign Keys
Foreign Keys prevent the deletion, or “Dropping”, of a table or data within a table, so if you’re looking to clean up shop and do away with a table, be sure to erase all the reference points first.
Final Thoughts
There you have the basic principles of Foreign Keys in a database. There is, of course, a lot more to learn about them and how they can be implemented across more complex data objects, but now you have the general gist, the foundation is set for you to become a veritable Foreign Key wiz!
Here at Open Query I want to make it as easy as possible for you to learn about topics that might not be easily accessible elsewhere online. I don’t know about you, but I haven’t seen many other websites talking about the MariaDB Foundation and its updates.
That’s what makes Open Query truly unique. While others might assume everyone knows this information already automatically, I am filling my online resource to help.
- What Is Percentile Rank? - February 15, 2022
- How To Use Distinct In SQL - February 14, 2022
- How To Drop A Column In SQL - February 14, 2022