What Is a Database Relationship?

Database relationships are the backbone of all relational databases

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:

Course_ID Course_Name Teacher_ID
Course_001 Biology Teacher_001
Course_002 Math Teacher_002
Course_003 English Teacher_003

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

Teacher_ID Teacher_Name
Teacher_001 Carmen
Teacher_002 Veronica
Teacher_003 Jorge

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

A chart on a computer screen
Glow / Getty Images

Types of Database Relationships

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


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.


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.


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:

Teacher_ID Teacher_Name Course
Teacher_001 Carmen Biology, Math
Teacher_002 Veronica Math
Teacher_003 Jorge English

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:

Teacher_ID Teacher_Name Course
Teacher_001 Carmen Biology
Teacher_001 Carmen Math
Teacher_002 Veronica Math
Teacher_003 Jorge English

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.

Was this page helpful?