{"id":91,"date":"2022-02-11T10:17:40","date_gmt":"2022-02-11T10:17:40","guid":{"rendered":"https:\/\/openquery.com\/?p=91"},"modified":"2022-02-28T13:55:58","modified_gmt":"2022-02-28T13:55:58","slug":"what-is-a-foreign-key-in-database","status":"publish","type":"post","link":"https:\/\/openquery.com\/what-is-a-foreign-key-in-database\/","title":{"rendered":"What Is A Foreign Key In Database?"},"content":{"rendered":"\n

The really great thing about databases is their structure. They keep all our data logged logically, making information retrieval and management easier \u2014 hooray! <\/p>\n\n\n\n

A single table in a database can help somebody run an entire aspect of their business, which is amazing, but here\u2019s the thing\u2026 sometimes, it\u2019s just not efficient to use a single table for a single application, and that\u2019s where Foreign Keys come into play.<\/p>\n\n\n\n

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 \u201cdependency\u201d between certain attributes. Confused? I really don\u2019t blame you. Let\u2019s discuss the matter in more detail.<\/p>\n\n\n\n

\"Foreign<\/figure><\/div>\n\n\n\n

Foreign And Primary Keys: An Example<\/strong><\/h2>\n\n\n\n

The first thing to understand about Foreign Keys is that they\u2019re never lone wolves, and they almost never partner with each other. In nearly every circumstance, Foreign Keys <\/em>work in conjunction with a Primary Key, and I\u2019ll explain the difference as we move through the following example.<\/p>\n\n\n\n

Let\u2019s say that we\u2019re running a business, and we\u2019re using a single table called Orders<\/em> in our database to keep track of, you\u2019ve guessed it\u2026 orders. Perhaps the columns in our table include Order ID, Customer Name, Customer Address, and Order(s).<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

In our table, we\u2019re going to start developing duplicate or \u201credundant\u201d data in a few of our columns, namely, the Customer Name and Customer Address columns, and this\u2026 this can be a problem.<\/p>\n\n\n\n

Problems With Redundant Data<\/strong><\/h3>\n\n\n\n

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<\/em> 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\u2019s why\u2026<\/p>\n\n\n\n

Redundant Data Is A Waste Of Space And Memory<\/strong><\/h3>\n\n\n\n

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<\/a>.<\/p>\n\n\n\n

Redundant Data Can Lead To Operational Mishaps<\/strong><\/h3>\n\n\n\n

Okay, so let\u2019s 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\u2019re scanning our order table, we see his name next to one of his older addresses and mistake it for his current one. We\u2019re then going to send out Joe\u2019s order to the wrong place \u2014 sorry, Joe.<\/p>\n\n\n\n

If we\u2019re 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 <\/em>work for it<\/em>, rather than the other way round, which defies the object of its existence.<\/p>\n\n\n\n

To completely eliminate the chances of human error during data retrieval and product dispatch, we\u2019d have to set up two tables linked by Foreign and Primary Keys.<\/p>\n\n\n\n

The Difference Between Foreign And Primary Keys<\/strong><\/h3>\n\n\n\n

Now that we\u2019re 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.<\/p>\n\n\n\n

The reason being, we\u2019re going to put the other stuff in our second table, which we\u2019ll call Customers. <\/em>Our Customers<\/em> table will also have a Customer ID column, as well as a Customer Name column and a Customer Address column.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

But then\u2026 if that\u2019s 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.<\/p>\n\n\n\n

Customer ID in our first table does not determine the data in the Order ID and Order columns, but as it\u2019s related to the Primary Key, where the field \u201coriginates\u201d, it\u2019s still considered a Key\u2026 a Foreign Key.<\/p>\n\n\n\n

To save confusion during your further reading on this topic, as the Foreign Key is referencing the Primary<\/a> Key, it may also be referred to as the referencing key, while the primary key may be referred to as the referenced key.<\/p>\n\n\n\n

Furthermore, the table holding the Foreign Key is known as the Child Table, while the other, holding the Primary Key<\/a>, is known as the Parent Table.<\/p>\n\n\n\n

How Does The Connection Between These Keys Streamline Our Database?<\/strong><\/h2>\n\n\n\n

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\u2019s not the main advantage here.<\/p>\n\n\n\n

The Foreign Key always references, and therefore, guides us to, a unique <\/em>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!<\/p>\n\n\n\n

Consequently, all our customers will receive their orders in a timely manner, and we won\u2019t 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!<\/p>\n\n\n\n

\u201cDropping\u201d Tables With Foreign Keys<\/strong><\/h2>\n\n\n\n

Foreign Keys prevent the deletion, or \u201cDropping\u201d, of a table or data within a table, so if you\u2019re looking to clean up shop and do away with a table, be sure to erase all the reference points first.<\/p>\n\n\n\n

Final Thoughts<\/strong><\/h2>\n\n\n\n

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!<\/p>\n\n\n\n

\n
https:\/\/www.youtube.com\/watch?v=5Rd2atcDR4s<\/a>