How To Create One-to-Many Relationships in SQL
Tags: sql, sqlrelationships, tutorial, sqlserver
Published: 2024-02-13 22:20:24

<!-- wp:heading -->
<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>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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://five.co/blog/how-to-create-many-to-many-relationships-in-sql/" data-type="post" data-id="1824">many-to-many relationship</a>. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In this blog post, we will learn how to create one-to-many relationships in SQL. We will use Five, a SQL-baed development environment, to do so. </p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:rank-math/toc-block {"headings":[{"key":"9114b11b-c841-42c5-bd4f-d2f11c8f7e6b","content":"Learn How to Implement One-To-Many Relationships With an Easy Example","level":2,"link":"#learn-how-to-implement-one-to-many-relationships-with-an-easy-example","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"62f87f61-a216-4b5b-9c17-83925fd9c017","content":"Understanding One-to-Many Relationships","level":2,"link":"#understanding-one-to-many-relationships","disable":true,"isUpdated":false,"isGeneratedLink":true},{"key":"1fc04cca-3bb3-44be-bd23-c4e8eae13d90","content":"Implementing a One-to-Many Relationship in SQL","level":2,"link":"#implementing-a-one-to-many-relationship-in-sql","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"bac8102b-1eed-4601-86c3-07e1836f3e33","content":"Create a Relational Database in SQL with Five","level":4,"link":"#create-a-relational-database-in-sql-with-five","disable":true,"isUpdated":false,"isGeneratedLink":true},{"key":"b90cadf0-7ef9-491c-89e3-f8a4178a103c","content":"Step 1: Creating the Country and City Tables","level":3,"link":"#step-1-creating-the-country-and-city-tables","disable":true,"isUpdated":false,"isGeneratedLink":true},{"key":"0f534f27-7d5a-42d2-af33-8917e249eb31","content":"Step 2: Relating the Two Tables to Each Other","level":3,"link":"#step-2-relating-the-two-tables-to-each-other","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"8c458cdf-0a1c-4adf-9ffd-36e9a3e84d96","content":"Using Five to Create MySQL Databases","level":2,"link":"#using-five-to-create-my-sql-databases","disable":true,"isUpdated":false,"isGeneratedLink":true},{"key":"287ee664-dda6-4340-a659-c7dd30031f3f","content":"1. Visually Create Tables in Five","level":3,"link":"#1-visually-create-tables-in-five","disable":true,"isUpdated":false,"isGeneratedLink":true},{"key":"3e974ff1-7d69-4e3d-8931-6d7f8055f736","content":"2. Let Five Auto-Generate Primary Keys","level":3,"link":"#2-let-five-auto-generate-primary-keys","disable":true,"isUpdated":false,"isGeneratedLink":true},{"key":"c08edcf8-b6c2-45dd-8112-da19f5249558","content":"3. Use Five to Create Table Relationships","level":3,"link":"#3-use-five-to-create-table-relationships","disable":true,"isUpdated":false,"isGeneratedLink":true},{"key":"ccba5922-8860-40a0-b420-afa2e27de07a","content":"4. Visually Inspect Your Database Schema ","level":3,"link":"#4-visually-inspect-your-database-schema","disable":false,"isUpdated":false,"isGeneratedLink":true},{"key":"af9737bd-0053-4698-93fb-36517a1ad304","content":"Recap: How to Implement One-to-Many Relationships in SQL","level":2,"link":"#recap-how-to-implement-one-to-many-relationships-in-sql","disable":false,"isUpdated":false,"isGeneratedLink":true}],"listStyle":"ul"} -->
<div class="wp-block-rank-math-toc-block" id="rank-math-toc"><nav><ul><li class=""><a href="#learn-how-to-implement-one-to-many-relationships-with-an-easy-example">Learn How to Implement One-To-Many Relationships With an Easy Example</a></li><li class=""><a href="#implementing-a-one-to-many-relationship-in-sql">Implementing a One-to-Many Relationship in SQL</a><ul></ul></li><li class=""><a href="#recap-how-to-implement-one-to-many-relationships-in-sql">Recap: How to Implement One-to-Many Relationships in SQL</a></li></ul></nav></div>
<!-- /wp:rank-math/toc-block -->

<!-- wp:heading -->
<h2 class="wp-block-heading" id="understanding-one-to-many-relationships">Understanding One-to-Many Relationships</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>For example, let's say we have been given the task to build 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>
<!-- /wp:paragraph -->

<!-- wp:list {"ordered":true} -->
<ol><!-- wp:list-item -->
<li>A <strong>Country </strong>has a country name and population, and </li>
<!-- /wp:list-item -->

<!-- wp:list-item -->
<li>A <strong>City</strong> has a city name, a city population, and a postcode.</li>
<!-- /wp:list-item --></ol>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>How do we implement this in our database schema? Let's get started by thinking about our tables and entity relationship diagram.</p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading -->
<h2 class="wp-block-heading" id="implementing-a-one-to-many-relationship-in-sql">Implementing a One-to-Many Relationship in SQL</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading {"textAlign":"center","level":4} -->
<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>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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!</p>
<!-- /wp:paragraph -->

<!-- wp:tadv/classic-paragraph -->
<div style="background-color: #001524;"><hr style="height: 5px;" />
<pre style="text-align: center; overflow: hidden; white-space: pre-line;"><span style="color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);"><strong>Sign Up For a Free Download</strong> <br /><span style="font-size: 14pt;">Download Five Today and Start Developing</span></span></pre>
<p style="text-align: center;"><a href="https://five.co/sign-up" 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>Free Download</strong></button><br /></a></p>
<hr style="height: 5px;" /></div>
<!-- /wp:tadv/classic-paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:paragraph -->
<p>Follow the steps below to create a one-to-many relationship between two tables. Let's begin and create our database.</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="step-1-creating-the-country-and-city-tables">Step 1: Creating the Country and City Tables</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Here's what our tables look like:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":2374,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/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"/></figure>
<!-- /wp:image -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:paragraph -->
<p>What's the most important field in each one of these tables? </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>It'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's the CityKey.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The primary key is what we're using to create a one-to-many relationship between our tables. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>But before we continue, here's the SQL statement to create the two tables above:</p>
<!-- /wp:paragraph -->

<!-- wp:kevinbatdorf/code-block-pro {"code":"\u002d\u002d Create a new database (if it doesn't exist)\nCREATE DATABASE IF NOT EXISTS YourDatabaseName;\n\n\u002d\u002d Use the newly created database\nUSE YourDatabaseName;\n\n\u002d\u002d Create the Country table\nCREATE TABLE Country (\n    CountryKey INT PRIMARY KEY,\n    Name VARCHAR(255),\n    Population INT\n);\n\n\u002d\u002d Create the City table\nCREATE TABLE City (\n    CityKey INT PRIMARY KEY,\n    Name VARCHAR(255),\n    CityPopulation INT,\n    Postcode VARCHAR(10)\n);\n","codeHTML":"\u003cpre class=\u0022shiki one-dark-pro\u0022 style=\u0022background-color: #282c34\u0022 tabindex=\u00220\u0022\u003e\u003ccode\u003e\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Create a new database (if it doesn\u0026#39;t exist)\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eDATABASE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eIF\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eNOT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eEXISTS\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e YourDatabaseName;\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Use the newly created database\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eUSE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e YourDatabaseName;\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Create the Country table\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eTABLE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eCountry\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    CountryKey \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePRIMARY KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eName\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePopulation\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e);\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #7F848E; font-style: italic\u0022\u003e\u002d\u002d Create the City table\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eTABLE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eCity\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    CityKey \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePRIMARY KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eName\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    CityPopulation \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    Postcode \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e10\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e)\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e);\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\u003c/code\u003e\u003c/pre\u003e","language":"sql","theme":"one-dark-pro","bgColor":"#282c34","textColor":"#abb2bf","fontSize":".875rem","fontFamily":"Code-Pro-JetBrains-Mono","lineHeight":"1.25rem","clampFonts":false,"lineNumbers":false,"headerType":"headlights","disablePadding":false,"footerType":"none","enableMaxHeight":false,"seeMoreType":"","seeMoreString":"","seeMoreAfterLine":"","seeMoreTransition":false,"highlightingHover":false,"lineHighlightColor":"rgba(134, 167, 228, 0.2)","copyButton":true,"copyButtonType":"heroicons","useTabs":false} -->
<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)
CREATE DATABASE IF NOT EXISTS YourDatabaseName;

-- Use the newly created database
USE YourDatabaseName;

-- Create the Country table
CREATE TABLE Country (
    CountryKey INT PRIMARY KEY,
    Name VARCHAR(255),
    Population INT
);

-- Create the City table
CREATE TABLE City (
    CityKey INT PRIMARY KEY,
    Name VARCHAR(255),
    CityPopulation INT,
    Postcode VARCHAR(10)
);
" 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>
<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>
<span class="line"></span>
<span class="line"><span style="color: #7F848E; font-style: italic">-- Use the newly created database</span></span>
<span class="line"><span style="color: #C678DD">USE</span><span style="color: #ABB2BF"> YourDatabaseName;</span></span>
<span class="line"></span>
<span class="line"><span style="color: #7F848E; font-style: italic">-- Create the Country table</span></span>
<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>
<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>
<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>
<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>
<span class="line"><span style="color: #ABB2BF">);</span></span>
<span class="line"></span>
<span class="line"><span style="color: #7F848E; font-style: italic">-- Create the City table</span></span>
<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>
<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>
<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>
<span class="line"><span style="color: #ABB2BF">    CityPopulation </span><span style="color: #C678DD">INT</span><span style="color: #ABB2BF">,</span></span>
<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>
<span class="line"><span style="color: #ABB2BF">);</span></span>
<span class="line"></span></code></pre></div>
<!-- /wp:kevinbatdorf/code-block-pro -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<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>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>So far, we have two tables without a relationship. Now, let'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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>By inserting the CountryKey as a foreign key into the city table, we associate each city with a country (remember: one table's primary key column is another table's foreign key column). </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Here's what our database schema looks like once we have created the one-to-many relationship.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":2375,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/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"/></figure>
<!-- /wp:image -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:paragraph -->
<p>Note that now we have a relationship between these two tables, shown by the line between the two tables. The "crow's feet" 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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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'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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The advantage of storing data in related tables now becomes obvious: a city doesn't need to "know" which country it belongs to or what that country's population is. It only needs to know the country's foreign key to retrieve this information from the Country table. This makes data storage, retrieval, and querying quick and efficient.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Here is the SQL to create the two tables with the relationship:</p>
<!-- /wp:paragraph -->

<!-- wp:kevinbatdorf/code-block-pro {"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);\n","codeHTML":"\u003cpre class=\u0022shiki one-dark-pro\u0022 style=\u0022background-color: #282c34\u0022 tabindex=\u00220\u0022\u003e\u003ccode\u003e\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eTABLE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eCountry\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    CountryKey \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePRIMARY KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eName\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePopulation\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e);\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eCREATE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eTABLE\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #61AFEF\u0022\u003eCity\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    CityKey \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003ePRIMARY KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eName\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e255\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    CityPopulation \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    Postcode \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eVARCHAR\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e(\u003c/span\u003e\u003cspan style=\u0022color: #D19A66\u0022\u003e10\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e),\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    CountryKey \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eINT\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e,\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e    \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eFOREIGN KEY\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e (CountryKey) \u003c/span\u003e\u003cspan style=\u0022color: #C678DD\u0022\u003eREFERENCES\u003c/span\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e Country(CountryKey)\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003cspan style=\u0022color: #ABB2BF\u0022\u003e);\u003c/span\u003e\u003c/span\u003e\n\u003cspan class=\u0022line\u0022\u003e\u003c/span\u003e\u003c/code\u003e\u003c/pre\u003e","language":"sql","theme":"one-dark-pro","bgColor":"#282c34","textColor":"#abb2bf","fontSize":".875rem","fontFamily":"Code-Pro-JetBrains-Mono","lineHeight":"1.25rem","clampFonts":false,"lineNumbers":false,"headerType":"headlights","disablePadding":false,"footerType":"none","enableMaxHeight":false,"seeMoreType":"","seeMoreString":"","seeMoreAfterLine":"","seeMoreTransition":false,"highlightingHover":false,"lineHighlightColor":"rgba(134, 167, 228, 0.2)","copyButton":true,"copyButtonType":"heroicons","useTabs":false} -->
<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 (
    CountryKey INT PRIMARY KEY,
    Name VARCHAR(255),
    Population INT
);

CREATE TABLE City (
    CityKey INT PRIMARY KEY,
    Name VARCHAR(255),
    CityPopulation INT,
    Postcode VARCHAR(10),
    CountryKey INT,
    FOREIGN KEY (CountryKey) REFERENCES Country(CountryKey)
);
" 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>
<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>
<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>
<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>
<span class="line"><span style="color: #ABB2BF">);</span></span>
<span class="line"></span>
<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>
<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>
<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>
<span class="line"><span style="color: #ABB2BF">    CityPopulation </span><span style="color: #C678DD">INT</span><span style="color: #ABB2BF">,</span></span>
<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>
<span class="line"><span style="color: #ABB2BF">    CountryKey </span><span style="color: #C678DD">INT</span><span style="color: #ABB2BF">,</span></span>
<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>
<span class="line"><span style="color: #ABB2BF">);</span></span>
<span class="line"></span></code></pre></div>
<!-- /wp:kevinbatdorf/code-block-pro -->

<!-- wp:paragraph -->
<p>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>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading -->
<h2 class="wp-block-heading" id="using-five-to-create-my-sql-databases">Using Five to Create MySQL Databases</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Five is an easy-to-use database application builder that comes with a graphical interface for creating a MySQL database. For a free download, <a href="https://five.co/sign-up">sign up here</a>. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Five makes it easy to create a MySQL database because of its powerful, visual database features. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><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>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="1-visually-create-tables-in-five">1. Visually Create Tables in Five</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Using Five'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>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In short, Five'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>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="2-let-five-auto-generate-primary-keys">2. Let Five Auto-Generate Primary Keys</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Five automatically creates primary keys, so you don't have to worry about them. Given the importance of primary keys for database design, this is a great time saver for developers. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Primary keys generated by Five are GUIDs, and the chance of ever having two primary keys being the same is virtually zero.</p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="3-use-five-to-create-table-relationships">3. Use Five to Create Table Relationships</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Creating relationships between tables is also really easy in Five.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>When creating a new table using the Table Wizard, Five will ask you whether you'd like to establish any relationships between tables. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading {"level":3} -->
<h3 class="wp-block-heading" id="4-visually-inspect-your-database-schema">4. Visually Inspect Your Database Schema </h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Your Entity-Relationship Diagram can be viewed in Five, using Five's <strong>visual database modeler</strong>. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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's database modeler is a handy tool to figure out how different tables are related to each other. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>All of the screenshots shown above of the City and Country tables were created using Five's database modeler.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":1613,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://five.co/wp-content/uploads/2023/08/Five.Co-Database-Modeler-1024x576.png" alt="Five.Co - Database Modeler for MySQL. The Modeler shows relationships between tables." class="wp-image-1613"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Overall, Five makes SQL easy and accessible: you don't need to struggle with queries, complicated setups, or credentials. Five is a simple database builder (<a href="https://five.co/features/" data-type="page" data-id="660">and so much more</a>).</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>To get started, <a href="https://five.co/sign-up">download Five</a> and follow our tutorials, such as this <a href="https://five.co/blog/excel-to-web-app/" data-type="post" data-id="1452">beginner-friendly Excel to web app tutorial</a>!</p>
<!-- /wp:paragraph -->

<!-- wp:separator -->
<hr class="wp-block-separator has-alpha-channel-opacity"/>
<!-- /wp:separator -->

<!-- wp:heading -->
<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>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Here'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 be described with the sentence "One of x can have many of y". In our case: one country can have many cities. The table storing your y's must contain the foreign key of x.</p>
<!-- /wp:paragraph -->