Want to keep learning?

This content is taken from the Raspberry Pi Foundation & National Centre for Computing Education's online course, Introduction to Databases and SQL. Join the course to learn more.

Skip to 0 minutes and 3 seconds When reviewing the computer sales database, you may have noticed that there’s a relationship between the manufacturer and several computer models. Let’s take a look at this in the Model table. So every computer in the Model table has a manufacturer ID.

Skip to 0 minutes and 24 seconds But the manufacturer ID also exists in the Manufacturer table. So each manufacturer has its own unique manufacturer ID. Remember, each entry in every table has a unique primary key. If we look on the database structure, we can see that in the Manufacturer table, manufacturer ID is the primary key. But the manufacturer ID also exists in the Model table. This is known as a foreign key. Any primary key, which exists within another table is known as a foreign key for that table. Foreign keys are how databases represent relationships between tables. While a table can only have one primary key, it can have multiple foreign keys– one for each table it has a relationship with.

Skip to 1 minute and 22 seconds Any foreign key in a table must be paired with a corresponding primary key. For example, in the Model and Manufacturer tables, a model cannot be entered if there isn’t any corresponding manufacturer. This is known as a foreign key constraint. You can test this for yourself. Let’s have a look at this SQL statement. So we’re going to insert model ID, manufacturer ID, and name into the Model table. But we’ve given the manufacturer ID a value a 99. This doesn’t exist in the Manufacturer table. Let’s see if we just run and execute this. There we go, we have a foreign key constraint error.

Skip to 2 minutes and 10 seconds The relationship between the Manufacturer and the Model tables is known as a one to many relationship, as one manufacturer can have many models. What is the relationship between the Model and the Sales table? Explore the three types of relationship in the article below, and discuss your answer in the comments section.

Relationships

You have already explored the structure of individual tables and learnt how to retrieve data from a table. In this next activity, you will explore the relationships between tables and find out how tables can be joined.

When reviewing the computer sales database, you may have noticed that there is a relationship between a manufacturer and several models.

Every computer in the model table has a manufacturer_id that matches a manufacturer_id in the manufacturer table. This is how you know which manufacturer created each model of computer.

a diagram showing the model and manufacturer tables with the manufacturer_id field highlighted in both and a link between them

The relationship between models and manufacturers could also be expressed as: a manufacturer can have many models.

Foreign keys

Every table should have a primary key that is unique for each record in the table.

manufacturer_id is the primary key for the manufacturer table, and manufacturer_id also exists in the model table. Within the model table, manufacturer_id is known as a foreign key.

Any primary key that exists within another table is known as a foreign key for that table. Foreign keys are how databases represent relationships between tables.

a diagram showing the model and manufacturer tables. In the manufacturer table the manufacturer_id is labeled primary key and the model_id is labelled foreign key. In the model table the model_id is labelled primary key.

While a table can have only one primary key, it can have multiple foreign keys, one for each table it has a relationship with.

Foreign keys in a table must be populated with accurate data that links to its corresponding primary key. For the model and manufacturer tables, this means that a model cannot be entered if there isn’t a corresponding manufacturer, because a model must have a manufacturer. This is known as a foreign key constraint.

You can test this by trying to insert a record into the model table with a manufacturer_id that doesn’t exist in the manufacturer table.

INSERT INTO model(model_id,
                  manufacturer_id,
                  name)
VALUES (99,
        99,
        'A model without a manufacturer');

Running this SQL will result in a FOREIGN KEY constraint failed error because there is no record in the manufacturer table with a manufacturer_id of 99.

Types of relationship

The relationship between the manufacturer and model tables is known as a one-to-many relationship, as one manufacturer can have many models.

There are three types of relationship:

  • One-to-many: one record in a table can relate to one or more records in another table, as in the example above; this is the most common type of relationship, for example, the relationship of computer manufacturer to models (“one computer manufacturer can have many models”)
  • One-to-one: one record in one table can only relate to one record in another table, for example, sales to returns (“one sale can only be returned once”)
  • Many-to-many: one or more records can relate to one or more records in another table, for example, computer models to components (“a model will have many components, and a component can be used in many models”)

What do you think the relationship between the model and sales table is? How did you determine this? Leave your answer in the comments section.

Share this video:

This video is from the free online course:

Introduction to Databases and SQL

Raspberry Pi Foundation