# Relationships

Describes relationships between tables within in a database and the importance of foreign keys.
2.6
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.
23.6
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.
81.7
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.
130.1
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.

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.

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.

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.