What Is a Database Relationship?

A relationship is established between two database tables when one table uses a foreign key that references the primary key of another table. This is the basic concept behind the term relational database.

How a Foreign Key Works to Establish a Relationship

A primary key uniquely identifies each record in the table. It is a type of candidate key that is usually the first column in a table and can be automatically generated by the database to ensure that it is unique. A foreign key is another candidate key (not the primary key) used to link a record to data in another table.

For example, consider these two tables that identify which teacher teaches which course. Here, the Courses table's primary key is Course_ID. Its foreign key is Teacher_ID:

You can see that the foreign key in Courses matches a primary key in Teachers:

We can say that the Teacher_ID foreign key helped establish a relationship between the Courses and the Teachers tables.

Types of Database Relationships

Using foreign keys, or other candidate keys, you can implement three types of relationships between tables:

One-to-One

This type of relationship allows only one record on each side of the relationship. The primary key relates to only one record (or none) in another table. For example, in a marriage, each spouse has only one other spouse. This kind of relationship can be implemented in a single table and therefore does not use a foreign key.

One-to-Many

A one-to-many relationship allows a single record in one table to be related to multiple records in another table. Consider a business with a database that has Customers and Orders tables.

A single customer can purchase multiple orders, but a single order could not be linked to multiple customers. Therefore the Orders table would contain a foreign key that matched the primary key of the Customers table, while the Customers table would have no foreign key pointing to the Orders table.

Many-to-Many

This is a complex relationship in which many records in a table can link to many records in another table. For example, our business probably needs Customers and Orders tables, and likely also needs a Products table.

Again, the relationship between the Customers and Orders table is one-to-many, but consider the relationship between the Orders and Products table. An order can contain multiple products, and a product could be linked to multiple orders since several customers might submit an order that contains some of the same products. This kind of relationship requires three tables at a minimum. 

Why Are Database Relationships Important?

Establishing consistent relationships between database tables helps ensure data integrity, contributing to database normalization. For example, what if we did not link any tables through a foreign key and instead combined the data in the Courses and Teachers tables, like so:

This design is inflexible and violates the first principle of database normalization, First Normal Form, which states that each table cell should contain a single, discrete piece of data.

Or perhaps we decided to add a second record for Carmen, in order to enforce 1NF:

This is still a weak design, introducing unnecessary duplication and what is called data insertion anomalies, which means that it could contribute to inconsistent data. For example, if a teacher has multiple records, what if some data needs to be edited, but the person performing the data editing does not realize that multiple records exist? The table would then contain different data for the same individual, without any clear way to identify it or avoid it.

Breaking this table into two tables, Teachers and Courses, creates the proper relationship between the data and therefore helps ensure data consistency and accuracy.