{"id":2373,"date":"2024-02-08T12:37:55","date_gmt":"2024-02-08T04:37:55","guid":{"rendered":"https:\/\/five.co\/?p=2373"},"modified":"2025-04-15T16:27:04","modified_gmt":"2025-04-15T08:27:04","slug":"how-to-create-one-to-many-relationships-in-sql","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-create-one-to-many-relationships-in-sql\/","title":{"rendered":"How To Create One-to-Many Relationships in SQL"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"learn-how-to-implement-one-to-many-relationships-with-an-easy-example\">Learn How to Implement One-To-Many Relationships With an Easy Example<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Relational database systems, such as MySQL, PostgreSQL, or SQLite make storing and retrieving data quick and efficient. The underlying principle of a relational database is to store data in<strong> related tables<\/strong>, which avoids data redundancy. Tables can be related in several ways: in a one-to-one relationship, a <strong>one-to-many relationship<\/strong>, or in a <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-create-many-to-many-relationships-in-sql\/\" data-type=\"post\" data-id=\"1824\">many-to-many relationship<\/a>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>In this blog post, we will learn how to create one-to-many relationships in SQL. <\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(25px + 0.390625vw);\"><strong>SQL Made Simple - Try Five<\/strong><br \/><span style=\"font-size: 14pt;\">Five is the Easiest Way to Create, Model and Query a SQL Database<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Started<\/strong><\/button><\/a><br \/><span style=\"color: #ffffff; font-size: revert;\">___<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"text-decoration: underline;\"><strong><span style=\"color: #ffffff; text-decoration: underline;\">Here&#8217;s What You Can Do With Five<\/span><\/strong><\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #ffffff;\">Create and Model a SQL Database \u2705<br \/>Write or Build SQL Queries \u2705<br \/>Visualize Queries as Charts or in Dashboards \u2705<br \/>Add CRUD Permissions to Control Data Access \u2705<br \/>Host Your Database Online \u2705\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #f1ebda; background-color: #4588d8;\"><em><strong>&#8220;Five bridges the gap between SQL and the web, <br \/>allowing me to create full-stack applications almost entirely in SQL&#8221;<\/strong><\/em><\/span><br \/><span style=\"color: #ffffff;\">&#8211; Crag Jones, Database Administrator (DBA)<\/span><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-xqz1rfm wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-xqz1rfm \"><div class=\"eb-toc-container eb-toc-xqz1rfm  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Learn How to Implement One-To-Many Relationships With an Easy Example&quot;,&quot;text&quot;:&quot;Learn How to Implement One-To-Many Relationships With an Easy Example&quot;,&quot;link&quot;:&quot;learn-how-to-implement-one-to-many-relationships-with-an-easy-example&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Understanding One-to-Many Relationships&quot;,&quot;text&quot;:&quot;Understanding One-to-Many Relationships&quot;,&quot;link&quot;:&quot;understanding-one-to-many-relationships&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Implementing a One-to-Many Relationship in SQL&quot;,&quot;text&quot;:&quot;Implementing a One-to-Many Relationship in SQL&quot;,&quot;link&quot;:&quot;implementing-a-one-to-many-relationship-in-sql&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;Create a Relational Database in SQL with Five&quot;,&quot;text&quot;:&quot;Create a Relational Database in SQL with Five&quot;,&quot;link&quot;:&quot;create-a-relational-database-in-sql-with-five&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Step 1: Creating the Country and City Tables&quot;,&quot;text&quot;:&quot;Step 1: Creating the Country and City Tables&quot;,&quot;link&quot;:&quot;step-1-creating-the-country-and-city-tables&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Step 2: Relating the Two Tables to Each Other&quot;,&quot;text&quot;:&quot;Step 2: Relating the Two Tables to Each Other&quot;,&quot;link&quot;:&quot;step-2-relating-the-two-tables-to-each-other&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Using Five to Create MySQL Databases&quot;,&quot;text&quot;:&quot;Using Five to Create MySQL Databases&quot;,&quot;link&quot;:&quot;using-five-to-create-mysql-databases&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;1. Visually Create Tables in Five&quot;,&quot;text&quot;:&quot;1. Visually Create Tables in Five&quot;,&quot;link&quot;:&quot;1-visually-create-tables-in-five&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;2. Let Five Auto-Generate Primary Keys&quot;,&quot;text&quot;:&quot;2. Let Five Auto-Generate Primary Keys&quot;,&quot;link&quot;:&quot;2-let-five-auto-generate-primary-keys&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;3. Use Five to Create Table Relationships&quot;,&quot;text&quot;:&quot;3. Use Five to Create Table Relationships&quot;,&quot;link&quot;:&quot;3-use-five-to-create-table-relationships&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;4. Visually Inspect Your Database Schema &quot;,&quot;text&quot;:&quot;4. Visually Inspect Your Database Schema &quot;,&quot;link&quot;:&quot;4-visually-inspect-your-database-schema&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Expansion: Build a Web App on a MySQL Database&quot;,&quot;text&quot;:&quot;Expansion: Build a Web App on a MySQL Database&quot;,&quot;link&quot;:&quot;expansion-build-a-web-app-on-a-mysql-database&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Recap: How to Implement One-to-Many Relationships in SQL&quot;,&quot;text&quot;:&quot;Recap: How to Implement One-to-Many Relationships in SQL&quot;,&quot;link&quot;:&quot;recap-how-to-implement-one-to-many-relationships-in-sql&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Learn How to Implement One-To-Many Relationships With an Easy Example&quot;,&quot;value&quot;:&quot;learn-how-to-implement-one-to-many-relationships-with-an-easy-example&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Understanding One-to-Many Relationships&quot;,&quot;value&quot;:&quot;understanding-one-to-many-relationships&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Implementing a One-to-Many Relationship in SQL&quot;,&quot;value&quot;:&quot;implementing-a-one-to-many-relationship-in-sql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Create a Relational Database in SQL with Five&quot;,&quot;value&quot;:&quot;create-a-relational-database-in-sql-with-five&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Step 1: Creating the Country and City Tables&quot;,&quot;value&quot;:&quot;step-1-creating-the-country-and-city-tables&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Step 2: Relating the Two Tables to Each Other&quot;,&quot;value&quot;:&quot;step-2-relating-the-two-tables-to-each-other&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Using Five to Create MySQL Databases&quot;,&quot;value&quot;:&quot;using-five-to-create-mysql-databases&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;1. Visually Create Tables in Five&quot;,&quot;value&quot;:&quot;1-visually-create-tables-in-five&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;2. Let Five Auto-Generate Primary Keys&quot;,&quot;value&quot;:&quot;2-let-five-auto-generate-primary-keys&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;3. Use Five to Create Table Relationships&quot;,&quot;value&quot;:&quot;3-use-five-to-create-table-relationships&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;4. Visually Inspect Your Database Schema &quot;,&quot;value&quot;:&quot;4-visually-inspect-your-database-schema&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Expansion: Build a Web App on a MySQL Database&quot;,&quot;value&quot;:&quot;expansion-build-a-web-app-on-a-mysql-database&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Recap: How to Implement One-to-Many Relationships in SQL&quot;,&quot;value&quot;:&quot;recap-how-to-implement-one-to-many-relationships-in-sql&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#understanding-one-to-many-relationships\">Understanding One-to-Many Relationships<\/a><li><a href=\"#implementing-a-one-to-many-relationship-in-sql\">Implementing a One-to-Many Relationship in SQL<\/a><li><a href=\"#using-five-to-create-mysql-databases\">Using Five to Create MySQL Databases<\/a><li><a href=\"#expansion-build-a-web-app-on-a-mysql-database\">Expansion: Build a Web App on a MySQL Database<\/a><li><a href=\"#recap-how-to-implement-one-to-many-relationships-in-sql\">Recap: How to Implement One-to-Many Relationships in SQL<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"understanding-one-to-many-relationships\">Understanding One-to-Many Relationships<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The real world is full of one-to-many relationships. Consequently, implementing this relationship in a database design is a common task for database administrators or anyone working with databases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, let&#8217;s say we have been given the task of building a system that keeps track of countries and cities. The system is supposed to have two tables: one for storing information about countries, and one for storing information about cities:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>A <strong>Country <\/strong>has a country name and population, and <\/li>\n\n\n\n<li>A <strong>City<\/strong> has a city name, a city population, and a postcode.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">This is a classic example of a one-to-many or 1:n relationship: one country has many cities. But cities can only be part of one country. One-to-many relationships can also apply to human resource systems (a company has many employees, but an employee is only part of one company), order management systems (a customer can place many orders, but each order is only associated with one customer), or libraries and books (one library has many books, but a book can only be part of one library).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">How do we implement this in our database schema? Let&#8217;s get started by thinking about our tables and entity relationship diagram.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"implementing-a-one-to-many-relationship-in-sql\">Implementing a One-to-Many Relationship in SQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To implement a one-to-many relationship we need to relate two tables to each other. We do so through <strong>Primary and Foreign Keys.<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading has-text-align-center\" id=\"create-a-relational-database-in-sql-with-five\">Create a Relational Database in SQL with Five<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Download Five for free to create a relational database, write queries, and build a user interface, such as a CRUD app on top of it! Once you have created your database, you can even export your database from Five as an SQL dump and keep working on it in MySQL Workbench or any other SQL GUI. <\/p>\n\n\n\n<div class=\"wp-block-buttons is-content-justification-center is-layout-flex wp-container-core-buttons-is-layout-fe48e5de wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button\"><a class=\"wp-block-button__link has-white-color has-vivid-cyan-blue-background-color has-text-color has-background has-link-color wp-element-button\" href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Free Sign Up<\/a><\/div>\n<\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Follow the steps below to create a one-to-many relationship between two tables. Let&#8217;s begin and create our database.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"step-1-creating-the-country-and-city-tables\">Step 1: Creating the Country and City Tables<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">First, create your Country and City table. As described above, a country has a country name and population. A city has a city name, city population and postcode.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s what our tables look like:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img width=\"1024\" height=\"520\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Database-Modeler-1024x520.png\" alt=\"Five.Co-One-to-Many-Relationship-Database-Modeler\" class=\"wp-image-2374\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Database-Modeler-1024x520.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Database-Modeler-300x152.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Database-Modeler-768x390.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Database-Modeler-1536x780.png 1536w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Database-Modeler.png 1749w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">What&#8217;s the most important field in each one of these tables? <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s the <strong>Primary Key<\/strong>, which in our database schema is marked by the little key icon. A primary key is a unique identifier of each record inside your table. In the Country table, the primary key is the CountryKey. In the City table, it&#8217;s the CityKey.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The primary key is what we&#8217;re using to create a one-to-many relationship between our tables. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But before we continue, here&#8217;s the SQL statement to create the two tables above:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"-- Create a new database (if it doesn't exist)\nCREATE DATABASE IF NOT EXISTS YourDatabaseName;\n\n-- Use the newly created database\nUSE YourDatabaseName;\n\n-- Create the Country table\nCREATE TABLE Country (\n    CountryKey INT PRIMARY KEY,\n    Name VARCHAR(255),\n    Population INT\n);\n\n-- Create the City table\nCREATE TABLE City (\n    CityKey INT PRIMARY KEY,\n    Name VARCHAR(255),\n    CityPopulation INT,\n    Postcode VARCHAR(10)\n);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a new database (if it doesn&#39;t exist)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">IF<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NOT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">EXISTS<\/span><span style=\"color: #ABB2BF\"> YourDatabaseName;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Use the newly created database<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">USE<\/span><span style=\"color: #ABB2BF\"> YourDatabaseName;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create the Country table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">Country<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CountryKey <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Population<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create the City table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">City<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CityKey <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CityPopulation <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    Postcode <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"step-2-relating-the-two-tables-to-each-other\">Step 2: Relating the Two Tables to Each Other<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">So far, we have two tables without a relationship. Now, let&#8217;s add the one-to-many relationship to our database. To do so, we must add the CountryKey as a <em>foreign key<\/em> into the City table. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By inserting the CountryKey as a foreign key into the city table, we associate each city with a country (remember: one table&#8217;s primary key column is another table&#8217;s foreign key column). <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s what our database schema looks like once we have created the one-to-many relationship.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img width=\"1024\" height=\"628\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Foreign-Key-1024x628.png\" alt=\"Five.Co-One-to-Many-Relationship-Foreign-Key\" class=\"wp-image-2375\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Foreign-Key-1024x628.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Foreign-Key-300x184.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Foreign-Key-768x471.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Foreign-Key-1536x942.png 1536w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-to-Many-Relationship-Foreign-Key.png 1804w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Note that now we have a relationship between these two tables, shown by the line between the two tables. The &#8220;crow&#8217;s feet&#8221; at the right end of the line show the direction of the relationship between our two entities: one country can have many cities, but not vice versa.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The City table now includes a column that contains the CountryKey for each record. This enables us to perform queries that utilize the join between the tables. For example, without the relationship we would not have been able to figure out what percentage of a country&#8217;s total population lives in a particular city. Now that the relationship is established we can do so <em>without<\/em> repeating any information. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The advantage of storing data in related tables now becomes obvious: a city doesn&#8217;t need to &#8220;know&#8221; which country it belongs to or what that country&#8217;s population is. It only needs to know the country&#8217;s foreign key to retrieve this information from the Country table. This makes data storage, retrieval, and querying quick and efficient.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here is the SQL to create the two tables with the relationship:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TABLE Country (\n    CountryKey INT PRIMARY KEY,\n    Name VARCHAR(255),\n    Population INT\n);\n\nCREATE TABLE City (\n    CityKey INT PRIMARY KEY,\n    Name VARCHAR(255),\n    CityPopulation INT,\n    Postcode VARCHAR(10),\n    CountryKey INT,\n    FOREIGN KEY (CountryKey) REFERENCES Country(CountryKey)\n);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">Country<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CountryKey <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Population<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">City<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CityKey <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CityPopulation <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    Postcode <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CountryKey <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FOREIGN KEY<\/span><span style=\"color: #ABB2BF\"> (CountryKey) <\/span><span style=\"color: #C678DD\">REFERENCES<\/span><span style=\"color: #ABB2BF\"> Country(CountryKey)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">One-to-many relationships are a fundamental building block of relational databases and we have just learned how to use them in our database design. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"using-five-to-create-my-sql-databases\">Using Five to Create MySQL Databases<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Five is an easy-to-use database application builder that comes with a graphical interface for creating a MySQL database. For a free trial, <a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\">sign up here<\/a>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five makes it easy to create a MySQL database because of its powerful, visual database features. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Creating the two tables to create a database with a one-to-many relationship can accomplished in Five with just a few clicks!<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"1-visually-create-tables-in-five\">1. Visually Create Tables in Five<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Five lets users create database tables visually and without writing SQL. Simply download Five, create a new app, and go to <strong>Data &gt; Table Wizard.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Using Five&#8217;s <strong>Table Wizard,<\/strong> creating the Country table, is as easy as adding fields and defining their data type. Five supports all standard SQL data types, from binary to boolean to string.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In short, Five&#8217;s visual table wizard replaces the tedious task of having to write out everything in SQL. It replaces CREATE statements with an intuitive table designer that covers all common SQL data types.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"2-let-five-auto-generate-primary-keys\">2. Let Five Auto-Generate Primary Keys<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Five automatically creates primary keys, so you don&#8217;t have to worry about them. Given the importance of primary keys for database design, this is a great time saver for developers. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Primary keys generated by Five are GUIDs, and the chance of ever having two primary keys being the same is virtually zero.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"3-use-five-to-create-table-relationships\">3. Use Five to Create Table Relationships<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Creating relationships between tables is also really easy in Five.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When creating a new table using the Table Wizard, Five will ask you whether you&#8217;d like to establish any relationships between tables. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Simply select the tables that your new table is related to, and Five will insert a Foreign Key for you. This comes in very handy when creating relationships.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"4-visually-inspect-your-database-schema\">4. Visually Inspect Your Database Schema <\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Your Entity-Relationship Diagram can be viewed in Five, using Five&#8217;s <strong>visual database modeler<\/strong>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This feature gives you a visual representation of your database, its tables, and relationships. If you are ever in doubt about your database structure, Five&#8217;s database modeler is a handy tool to figure out how different tables are related to each other. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">All of the screenshots shown above of the City and Country tables were created using Five&#8217;s database modeler.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img width=\"1024\" height=\"702\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/use-cases-database-modeler-1024x702.jpg\" alt=\"Five.Co - Database Modeler\" class=\"wp-image-1328\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/use-cases-database-modeler-1024x702.jpg 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/use-cases-database-modeler-300x206.jpg 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/use-cases-database-modeler-768x527.jpg 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/use-cases-database-modeler-1536x1053.jpg 1536w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/use-cases-database-modeler-2048x1405.jpg 2048w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Overall, Five makes SQL easy and accessible: you don&#8217;t need to struggle with hand-written SQL queries, complicated setups, or credentials. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\" id=\"expansion-build-a-web-app-on-a-my-sql-database\">Expansion: Build a Web App on a MySQL Database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Learn how to build an entire web app on a SQL database by following our <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/acs-masterclass\/\" target=\"_blank\" rel=\"noreferrer noopener\">step-by-step app development guide<\/a>. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The guide starts with the creation of a database from scratch and finishes with a web app that contains a chart, PDF reports, and forms for users to interact with your data. You will learn how to combine all these front-end elements with your back-end database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Best of all: it&#8217;s free to follow!<\/p>\n\n\n\n<div class=\"wp-block-buttons is-content-justification-center is-layout-flex wp-container-core-buttons-is-layout-fe48e5de wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button\"><a class=\"wp-block-button__link has-white-color has-vivid-cyan-blue-background-color has-text-color has-background has-link-color wp-element-button\" href=\"https:\/\/preview-php85.dzine.org\/five\/code-along\/code-along-full-stack-web-app\/\" target=\"_blank\" rel=\"noreferrer noopener\">Learn How to Build a Web App<\/a><\/div>\n<\/div>\n<\/div><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"recap-how-to-implement-one-to-many-relationships-in-sql\">Recap: How to Implement One-to-Many Relationships in SQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s our recap: one-to-many relationships are part and parcel of good relational database design. To implement a one-to-many relationship between two tables, insert a column that holds a foreign key into the table that is described by the sentence &#8220;One of x can have many of y&#8221;. In our case: one country can have many cities. The table storing your y&#8217;s must contain the foreign key of x.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn How to Implement One-To-Many Relationships With an Easy Example Relational database systems, such as MySQL, PostgreSQL, or SQLite make storing and retrieving data quick and efficient. The underlying principle of a relational database is to store data in related tables, which avoids data redundancy. Tables can be related in several ways: in a one-to-one [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":2377,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-2373","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-To-Many-Relationship-in-SQL-1.png",960,540,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-To-Many-Relationship-in-SQL-1-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-To-Many-Relationship-in-SQL-1-300x169.png",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-To-Many-Relationship-in-SQL-1-768x432.png",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-To-Many-Relationship-in-SQL-1.png",960,540,false],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-To-Many-Relationship-in-SQL-1.png",960,540,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/02\/Five.Co-One-To-Many-Relationship-in-SQL-1.png",960,540,false]},"uagb_author_info":{"display_name":"Dominik Keller","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed3_my\/"},"uagb_comment_info":0,"uagb_excerpt":"Learn How to Implement One-To-Many Relationships With an Easy Example Relational database systems, such as MySQL, PostgreSQL, or SQLite make storing and retrieving data quick and efficient. The underlying principle of a relational database is to store data in related tables, which avoids data redundancy. Tables can be related in several ways: in a one-to-one&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/2373","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=2373"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/2373\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/2377"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=2373"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=2373"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=2373"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}