Retrieve Data Using SQL

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.

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.

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:

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!

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.