{"id":179576,"date":"2024-05-13T20:31:51","date_gmt":"2024-05-13T19:31:51","guid":{"rendered":"https:\/\/liora.io\/en\/?p=179576"},"modified":"2026-02-06T08:02:13","modified_gmt":"2026-02-06T07:02:13","slug":"unlocking-the-power-of-sql-foreign-keys-definition-and-applications","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/unlocking-the-power-of-sql-foreign-keys-definition-and-applications","title":{"rendered":"Unlocking the Power of SQL Foreign Keys: Definition and Applications"},"content":{"rendered":"<style>\n.elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading-title[class*=elementor-size-]>a{color:inherit;font-size:inherit;line-height:inherit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading .elementor-heading-title.elementor-size-xxl{font-size:59px}<\/style><p><strong>If you&#8217;re designing an SQL database management system, it&#8217;s in your interest to know and implement the foreign key system. In the long term, it&#8217;s a win-win situation.<\/strong><\/p>\t\t\n\t\t<p>A foreign key is a column (or several columns) in a <a href=\"https:\/\/liora.io\/en\/olap-everything-you-need-to-know-about-this-database-organization\">database<\/a> that is linked to the primary key column of another table. The primary key in question is a simple identifier. The useful information &#8211; which is subject to change &#8211; is contained in the external table.<\/p>\t\t\n\t\t\t<h3>What use is a foreign key?<\/h3>\t\t\n\t\t<p>Let&#8217;s imagine you&#8217;re managing an <strong>Articles table,<\/strong> in which there&#8217;s a column called Category. For each item, this category must be filled in individually.<\/p><p>For example, we might have a category called &#8220;Metal product&#8221;. However, from one day to the next, legislation changes and this category becomes<strong> &#8220;Metal-based product&#8221;.<\/strong><\/p><p>What do we do now? Will we have to modify all the lines in which the &#8220;Metal product&#8221; category appears? Unfortunately, yes. If the Articles table covers tens of thousands of entries, the procedure will be tedious, to say the least.<\/p><p>It&#8217;s to avoid this kind of situation that the notion of foreign key has been defined. So, in the case mentioned above, the idea is to create a separate table, called Category, in which the primary key is an identifier (a number for each category) associated with the corresponding description.<\/p><p>From there, in the <strong>Articles table,<\/strong> all we need to do is indicate, for each product offered in the catalog, the corresponding identifier from the Categories table.<\/p><p>It will then be possible to print the list of each item while displaying (thanks to a join) the corresponding category.<\/p><p>So, as we can see, the situation of the category that changes its name is solved here.<\/p>\t\t\n\t\t\t<h3>Parent and daughter table<\/h3>\t\t\n\t\t<p>A &#8220;parent table&#8221; is one that is referenced by another table. This would be the case here of the <strong>Categories table.<\/strong><\/p><p>And we speak of a &#8220;daughter table&#8221; for one that refers to an external table &#8211; which would be the case here of the Articles table.<\/p>\t\t\n\t\t\t<h3>A practical example<\/h3>\t\t\n\t\t<p>Here&#8217;s another example of the use of a foreign key in a more explicit case.<\/p>\t\t\n\t\t\t<h4>CLIENTS table<\/h4>\t\t\n\t\t\t<style type=\"text\/css\">\n.tg  {border-collapse:collapse;border-spacing:0;}\n.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;\n  overflow:hidden;padding:10px 5px;word-break:normal;}\n.tg th{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;\n  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n.tg .tg-97aw{font-family:\"Trebuchet MS\", Helvetica, sans-serif !important;font-size:16px;text-align:center;vertical-align:top}\n.tg .tg-lyi1{font-family:\"Trebuchet MS\", Helvetica, sans-serif !important;font-size:16px;font-weight:bold;text-align:center;\n  vertical-align:top}\n<\/style>\n<table style=\"undefined;table-layout: fixed; width: 400px\">\n<colgroup>\n<col style=\"width: 100px\">\n<col style=\"width: 100px\">\n<col style=\"width: 100px\">\n<col style=\"width: 100px\">\n<\/colgroup>\n<thead>\n  <tr>\n    <th>Id_Client<\/th>\n    <th>Name<br>        <\/th>\n    <th>First Name<br>        <\/th>\n    <th>Age<br>        <\/th>\n  <\/tr>\n<\/thead>\n<tbody>\n  <tr>\n    <td>1<\/td>\n    <td>Martin<br>        <\/td>\n    <td>Germaine<br>        <\/td>\n    <td>32<br>        <\/td>\n  <\/tr>\n  <tr>\n    <td>2<br>        <\/td>\n    <td>Culprit<br>        <\/td>\n    <td>Thomas<br>        <\/td>\n    <td>22<br>        <\/td>\n  <\/tr>\n  <tr>\n    <td>3<br>        <\/td>\n    <td>Albanez<br>        <\/td>\n    <td>Francis<br>        <\/td>\n    <td>40<br>        <\/td>\n  <\/tr>\n  <tr>\n    <td>4<br>        <\/td>\n    <td>De Jonqui\u00e8re<br>        <\/td>\n    <td>Martine<br>        <\/td>\n    <td>33<br>        <\/td>\n  <\/tr>\n<\/tbody>\n<\/table>\n\t\t<p>CLIENTS is the &#8220;relative&#8221; table. If customer n\u00b04, Martine de Jonqui\u00e8re, were to divorce and resume her maiden name (Heras) tomorrow, we would simply modify this information in the &#8220;relative&#8221; table.<\/p>\t\t\n\t\t\t\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><div class=\"wp-block-button \"><a class=\"wp-block-button__link wp-element-button \" href=\"\/en\/courses\/data-ai\/\">Understanding foreign keys<\/a><\/div><\/div>\n\n\t\t\t<h4>Table COMMANDES<\/h4>\t\t\n\t\t\t<style type=\"text\/css\">\n.tg  {border-collapse:collapse;border-spacing:0;}\n.tg td{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;\n  overflow:hidden;padding:10px 5px;word-break:normal;}\n.tg th{border-color:black;border-style:solid;border-width:1px;font-family:Arial, sans-serif;font-size:14px;\n  font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}\n.tg .tg-97aw{font-family:\"Trebuchet MS\", Helvetica, sans-serif !important;font-size:16px;text-align:center;vertical-align:top}\n.tg .tg-lyi1{font-family:\"Trebuchet MS\", Helvetica, sans-serif !important;font-size:16px;font-weight:bold;text-align:center;\n  vertical-align:top}\n<\/style>\n<table style=\"undefined;table-layout: fixed; width: 375px\">\n<colgroup>\n<col style=\"width: 125px\">\n<col style=\"width: 150px\">\n<col style=\"width: 100px\">\n<\/colgroup>\n<thead>\n  <tr>\n    <th>Id_Commande<br>     <\/th>\n    <th>Date_Commande<br>      <\/th>\n    <th>Id_Client<br>       <\/th>\n  <\/tr>\n<\/thead>\n<tbody>\n  <tr>\n    <td>1<br>       <\/td>\n    <td>11\/04\/2023<br>      <\/td>\n    <td>4<br>       <\/td>\n  <\/tr>\n  <tr>\n    <td>2<br>       <\/td>\n    <td>10\/05\/2023<br>      <\/td>\n    <td>3<br>       <\/td>\n  <\/tr>\n  <tr>\n    <td>3<br>       <\/td>\n    <td>12\/05\/2023<br>      <\/td>\n    <td>4<br>       <\/td>\n  <\/tr>\n  <tr>\n    <td>4<br>       <\/td>\n    <td>26\/05\/2023<br>      <\/td>\n    <td>4<br>       <\/td>\n  <\/tr>\n  <tr>\n    <td>5<br>       <\/td>\n    <td>16\/06\/2023<br>      <\/td>\n    <td>1<br>       <\/td>\n  <\/tr>\n<\/tbody>\n<\/table>\n\t\t<p>In this example, we find the &#8220;Customer_ID&#8221; column in the ORDERS table (which is the &#8220;daughter&#8221; table). It points to the &#8220;Id_Client&#8221; column in the CLIENTS table. In this way, we would know by means of a join that Martine de Jonqui\u00e8res placed three orders: once in April and twice in June.<\/p><p>In the CUSTOMERS table, the Customer_ID column is the primary key.<\/p><p>In the ORDERS table, the Customer_ID column is the foreign key.<\/p>\t\t\n\t\t\t<h3>Advantages of foreign keys<\/h3>\t\t\n\t\t\t<h4>Referential integrity<\/h4>\t\t\n\t\t<p>The main advantage of using foreign keys is referential integrity: no row in the child table will be complete and valid until a corresponding record exists in the parent table.<\/p>\t\t\n\t\t\t<h4>Consistency of information<\/h4>\t\t\n\t\t<p>All tables referencing the foreign key are guaranteed to produce consistent, up-to-date data. As a reminder, if Martine de Jonqui\u00e8res changes her name, this change will be visible in all tables referencing her identifier.<\/p>\t\t\n\t\t\t<h3>Disadvantages of foreign keys<\/h3>\t\t\n\t\t\t<h4>Reduced processing speed<\/h4>\t\t\n\t\t<p>Of course, the foreign key will mean longer processing times. Each time you insert a new row in a table such as ORDERS, you&#8217;ll need to check whether the corresponding customer identifier exists in the CUSTOMERS table.<\/p>\t\t\n\t\t\t<h4>Complexity<\/h4>\t\t\n\t\t<p>Referring to customer_id n\u00b04 may appear more complex than simply indicating the name Martine de Jonqui\u00e8res. And so, a priori, reading a database such as the ORDERS database will not be very explicit at first.<\/p>\t\t\n\t\t\t<h3>Recommended use<\/h3>\t\t\n\t\t<p>Despite the disadvantages of using foreign keys, the use of foreign keys is highly recommended for anyone responsible for designing SQL database management systems. In the long term, it&#8217;s a win-win situation. To put it simply, it&#8217;s important to use foreign keys whenever you&#8217;re dealing with a type of data that is likely to evolve over time. The examples given here clearly illustrate what can be gained. And so, it&#8217;s crucial to define foreign keys right from the design stage of an SQL database.<\/p><p>&nbsp;<\/p><p>?Related articles:<\/p><table dir=\"ltr\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\" data-sheets-root=\"1\"><colgroup><col width=\"656\"><\/colgroup><tbody><tr><td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;SQL DELETE: How do I use this query?&quot;}\" data-sheets-hyperlink=\"https:\/\/liora.io\/en\/sql-delete-how-do-i-use-this-query\"><a href=\"https:\/\/liora.io\/en\/sql-delete-how-do-i-use-this-query\" target=\"_blank\" rel=\"noopener\">SQL DELETE: How do I use this query?<\/a><\/td><\/tr><tr><td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;SQL Developer: What is it? What's it for?&quot;}\" data-sheets-hyperlink=\"https:\/\/liora.io\/en\/sql-developer-what-is-it-whats-it-for\"><a href=\"https:\/\/liora.io\/en\/sql-developer-what-is-it-whats-it-for\" target=\"_blank\" rel=\"noopener\">SQL Developer: What is it? What&#8217;s it for?<\/a><\/td><\/tr><tr><td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;SQL Tutorial: Top 5 Most Useful Methods&quot;}\" data-sheets-hyperlink=\"https:\/\/liora.io\/en\/sql-tutorial-top-5-most-useful-methods\"><a href=\"https:\/\/liora.io\/en\/sql-tutorial-top-5-most-useful-methods\" target=\"_blank\" rel=\"noopener\">SQL Tutorial: Top 5 Most Useful Methods<\/a><\/td><\/tr><tr><td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;SQL joins: everything you need to know about table associations&quot;}\" data-sheets-hyperlink=\"https:\/\/liora.io\/en\/sql-joins-everything-you-need-to-know-about\"><a href=\"https:\/\/liora.io\/en\/sql-joins-everything-you-need-to-know-about\" target=\"_blank\" rel=\"noopener\">SQL joins: everything you need to know about table associations<\/a><\/td><\/tr><tr><td data-sheets-value=\"{&quot;1&quot;:2,&quot;2&quot;:&quot;SQL vs NoSQL: differences, uses, advantages and disadvantages&quot;}\" data-sheets-hyperlink=\"https:\/\/liora.io\/en\/sql-vs-nosql\"><a href=\"https:\/\/liora.io\/en\/sql-vs-nosql\" target=\"_blank\" rel=\"noopener\">SQL vs NoSQL: differences, uses, advantages and disadvantages<\/a><\/td><\/tr><\/tbody><\/table>\t\t\n\t\t\t\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><div class=\"wp-block-button \"><a class=\"wp-block-button__link wp-element-button \" href=\"\/en\/courses\/data-ai\/\">Learn to manage SQL databases<\/a><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;re designing an SQL database management system, it&#8217;s in your interest to know and implement the foreign key system. In the long term, it&#8217;s a win-win situation. A foreign key is a column (or several columns) in a database that is linked to the primary key column of another table. The primary key in [&hellip;]<\/p>\n","protected":false},"author":76,"featured_media":179578,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2434],"class_list":["post-179576","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud-dev"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/179576","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/76"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=179576"}],"version-history":[{"count":1,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/179576\/revisions"}],"predecessor-version":[{"id":205740,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/179576\/revisions\/205740"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/179578"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=179576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=179576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}