Relationships Between Tables with Primary Key and Foreign Key

4 min
Beginner

Relationships are logical links that connect two or more tables together based on columns they share.

Their fundamental goal is to structure information to avoid data duplication and ensure consistency throughout the system.

For example, if you come from the Excel world, you might be used to having a “super table” where you repeat data:

Order	Customer	Email	Address	Product
100	Juan Pérez	[email protected]	Calle Falsa 123	Bike
101	Juan Pérez	[email protected]	Calle Falsa 123	Helmet

But what happens if Juan changes his email? You have to update it in 500 rows. Or you might mess up and change only one of the emails by mistake.

Relational databases solve this by dividing information into separate tables (Customers, Orders, Products) and connecting them through Relationships.

The Main Characters: PK and FK

To create a relationship, we need two fundamental elements:

It is the unique and non-repeatable identifier of a row. It’s like the ID Card or Passport number of that record.

In the Customers table, CustomerID is the PK.
Juan Pérez has ID 1. There cannot be another customer with ID 1.

It is a field in another table that points to the Primary Key of the first one. It’s the “hook” or link.

In the Orders table, we add a column CustomerID.
That column is not unique (Juan can place many orders), but it must exist in the Customers table.

Types of Relationships

Depending on how the data interacts, there are three types of relationships. Let’s see them with examples.

One to Many Relationship (1)

This is, by far, the most common one (90% of the time).

The concept:

One Customer can place Many Orders.
But One Order belongs to only one Customer.

How it’s done: We put the Foreign Key on the “Many” side (the child table).

Thanks to the CustomerID (FK) being repeated in the Orders table, we know that Ana has bought twice.

Many to Many Relationship (N)

Here things get more complicated.

The concept:

One Student enrolls in Many Subjects.
One Subject has Many Students enrolled.

The problem: We cannot put an SubjectID column in Students (because they study several), nor an StudentID column in Subjects (because it has several students). We also cannot store a comma-separated list (1, 5, 8) because that violates database rules.

The solution: The Intermediate Table We create a third table (Bridge Table) whose sole purpose is to connect the other two.

When we do JOINS to connect Students with Subjects, we will have to go through this bridge table.

One to One Relationship (1:1)

It is the least frequent.

The concept:

One employee has One company mobile phone assigned.
That company mobile phone belongs only to that employee.

Generally, if the relationship is 1 to 1, we usually store the data in the same table. But sometimes they are separated for security or cleanliness (e.g., UsersTable and ConfidentialMedicalDataTable).

The FK can go in either of the two tables, marked as unique (UNIQUE).

Referential Integrity

Relationships are not only for querying data, they serve to protect it. Databases have a mechanism called Referential Integrity.

This means the database will not let you break the relationship:

You cannot create an Order with CustomerID = 99 if customer 99 does not exist in the Customers table. (Error: Foreign Key Constraint Violation).
You cannot delete the Customer “Juan” if he has associated orders in the Orders table (you would leave orphaned records). First, you would have to delete his orders.