• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 4/19/21

Create links between your relations using foreign keys

Log in or subscribe for free to enjoy all this course has to offer!

So far, we have seen a relation that contains apples. In a database, however, it is extremely rare for there to be just one table. Computer applications that use databases are generally require many tables.

Understand how tables are related

I may decide, for example, to have an apple relation as well as a second relation, variety, that lists the various known apple varieties. These two relations are linked, because an apple belongs to a certain variety. There is, therefore, a relationship between the apple and its variety.

At a produce store, the price of an apple can depend on its variety. Price per kilogram is therefore a characteristic of the variety. In our variety relation, we will call this attribute price_per_kilo.

Variety

label

price_per_kilo

maturity date

taste

Red Delicious

3.19

late Sept.

sweet

Braeburn

3.49

mid Oct.

sweet/tart

Gala

3.19

mid Sept.

sweet

McIntosh

2.99

mid Sept.

tart

[...]

[...]

[...]

[...]

If I want to know the price per kilo of a given apple, I must first know its variety. I will then need to locate the corresponding row in the variety table in order to know its price.

Locate the row in the table?!? Isn’t that what keys are for?

Indeed it is! Which brings me to the following section:

Link relations using foreign keys

In our variety relation, there is a candidate key: the label attribute.

Now, let's add a column to our apple table that provides the name of the variety for each apple. We could then use that key to find the price per kilo. This is what our new apple table might look like:

Apple

identifier

weight

diameter

color

name_variety

14

142 g

7.3 cm

red

Red Delicious

25

182 g

7.5 cm

red

Gala

16

140 g

7.9 cm

red

Red Delicious

[...]

[...]

[...]

[...]

[...]

In the apple table, the name_variety column is not a candidate key. Nevertheless, this column matches the primary key of another table (the variety table). Therefore, we can say that:

Now you know how to link tables using foreign keys!

"name_variety" is a foreign key referencing the "label" column of the variety table

Summary

  • The purpose of a foreign key is to create links between relations (= tables).

  • A foreign key of a table A is said to reference the primary key of a table B.

  • FK stands for foreign key.

Example of certificate of achievement
Example of certificate of achievement