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.”

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):

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:

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:

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:

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):

  1	INSERT INTO city (city_name, lat, long, country_id) VALUES ('Wien', 48.2084885, 16.3720798, 6);

  This is what happens:

  

  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:

  1	DELETE FROM country WHERE id = 5;

  The result is shown below:

  

  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

• Author
• Recent Posts

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

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020