Emil Drkusic

Learn SQL: Foreign Key

January 7, 2020 by

In the previous article, we talked about the primary key (PK). In this one, we’ll check what is the foreign key (FK) and how it’s related to the primary key. This is the final step, after creating tables, inserting data, and defining primary keys, to cover all basics related to the database theory. There is much, much more than this, but if you understand these concepts, everything else is just expanding your knowledge around these basics.

Foreign key graphical representation

Before we define what FK is, we’ll once more remind ourselves of what is the primary key:

“Primary key is a value, or a combination of few values from the table, uniquely defining each record in this table. If we know this value/combination, we can easily find the related record and access all remaining values from that record.”

database schema/diagram

In our model, both tables, country, and city have 1 attribute which is used as a primary key and that is the id attribute. The value of this attribute is defined to start from 1 and increment by 1 for each new row added. That way, the system automatically ensures that we don’t have duplicate values.

The one thing which is pretty obvious is that we have a line connecting our tables. This line goes from country.id to city.country_id and there is a good reason for that. This is how we graphically represent a FK.

What is a foreign key?

In the previous section, we concluded that there is some kind of relationship between our two tables. The attribute from one table (city.country_id) is related to the primary key attribute from another table (country.id). That attribute (city.country_id) is a FK attribute. The table country is the referenced table, and the table city is the child table. This relationship actually says that value stored in the city.country_id attribute should be one from the set of values stored in the country.id attribute (or, in some cases, although not this one, could be undefined = NULL).

So, the definition of the FK would be:

“The foreign key is an attribute, or more of them, directly related to the primary key of another table. When properly set, this rule shall ensure that we must always set the value of that attribute to exactly 1 value from the referenced table. This is the way how we relate data stored in different tables in our database model.”

Note: If primary keys are always defined as a single column, then each FK shall be exactly 1 attribute. While this might not look so important at first, it actually proves to be a good “rule” to follow when designing a database. We’ll talk more about that in the next article related to indices.

Foreign key – How it’s stored/defined in the database?

After the theory, it’s a good time to take a look at when is actually in the database. We’ll do it using the database we’ve created previously (same diagram as the one used in the first section of this article):

SQL Server - keys

After I clicked on the relationship city_country in Object explorer, the Foreign Key Relationship window popped up. Under General -> Tables And Columns Specification you can see the previously mentioned relation between 2 columns in out 2 tables. If we scroll down a little, we can also see the remaining properties:

SQL Server - keys

You can see the following settings:

  • Enforce for replication – possible values here are “Yes” and “No” – Enables or disables it for replication. Please read more about the replication in SQL Server Replication (Merge) – What gets replicated and what doesn’t article
  • Enforce Foreign Key Constraint – possible values are “Yes” and “No”. If it’s enabled, the FK constraint works as we want it. We could disable it if we want, but we should have a good reason for that. One could be that we want to manually change keys values and we wouldn’t be able to do that because constraints would “scream” all the time
  • Delete Rule – possible values are “No Action”, “Cascade”, “Set NULL”, and “Set Default” – defines what will happen with child table rows if we delete the related row from the referenced table
  • Update Rule – possible values are “No Action”, “Cascade”, “Set NULL”, and “Set Default” – defines what will happen with child table rows if we update the related row in the referenced table

Now we’ll examine the code to see how the FK was defined. Let’s right-click on the table dbo.city and then Script Table as | CREATE to | New Query Editor Window just as on the picture below:

SQL Server - script table as

The result is the following code:

You can see FK definitions in the red lines, with the first statement defining which attributes are included in the check and the second statement telling us this is related to the definition of the city table.

Foreign key – What it actually does?

First, let’s see the contents of our tables:

SQL Server - SELECT

FK will do a few checks, but most important are:

  • If you add a row to a table, the attribute which is part of the foreign key must have the pair in the original table (1:n). It can be NULL if the type of that relation (FK) is not mandatory (0:n). In any case, it can’t contain a value not existing in the referenced table

    In our case, this means, that we can’t add a city if the country_id is not in the range [1,5] – set of id values from the country table. If we try to execute something like this (notice that in this statement country_id = 6 and we don’t have a pair in the country table):

    This is what happens:

    SQL Server - INSERT fails

    And this is great because the FK prevented us from making a mistake

    Note: In case we could execute this INSERT statement, we would insert a city belonging to a country with id = 6, without actually knowing that country. Later, someone could insert e.g. the Netherlands as the country with id = 6 and we would have inconsistent data

  • When we delete data from the referenced table, we won’t be able to delete if there are related records in the child table. This stands when setting Delete Rule is set to “No Action” (this should be how you set your foreign key in most cases). “Cascade” would also delete rows from the child table, while “Set NULL” and “Set Default” wouldn’t delete entire rows from the child table, but just set the values of these attributes to NULL or predefined value

    Let’s try to delete country with id = 5 (Poland) using the statement:

    The result is shown below:

    SQL Server - DELETE fails

    Once again, this is really great because the defined rule (FK), prevented us from deleting something we still use

    Note: If the statement would delete Poland, then Warsaw would be assigned to the non-existing country. While we don’t know which country it belongs to, someone could later insert another country with id = 5 (as for inserts) and we would have a problem with our data.

The FK, when defined properly, instead of you, does the job in the background. It takes care of the referential integrity in our database.

Conclusion

Foreign keys, together with primary keys, relate data throughout our database. Keys are usually not data from the real-world (we actually have an interest only in the real-world data), but the values we generate automatically. Still, they are needed in order that everything runs (smoothly). This topic definitely requires more than 1 article and today we just scratched the surface. In order to explain this concept, we’ll talk about the types of relations, referential integrity, and indices in upcoming articles.

Table of contents

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server
Learn SQL: SQL Injection
Learn SQL: Dynamic SQL
Learn SQL: How to prevent SQL Injection attacks
Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic

168 Views