Introduction to Database Relationships

Relational databases support enforced, defined links between tables

The database terms relational and relationship describe the way that data in tables are connected. A relational database consists of a series of two or more tables that are linked by a specific key. A relational database differs from unstructured databases, which are more common in massive big-data initiatives. Relational databases tend to require strict rules about how tables are defined and what constitutes a valid relationship among tables.

Relationships allow you to describe the connections between different database tables in powerful ways. These relationships can then be leveraged to perform powerful cross-table queries, known as joins.

Businessmen talking, discussing data on laptop in conference room meeting
Hero Images/Getty Images

Types of Database Relationships

There are three types of database relationships, each named according to the number of table rows involved in the relationship. Each of these three relationship types exists between two tables.

  • One-to-one relationships occur when each entry in the first table has one, and only one, counterpart in the second table. One-to-one relationships are rarely used because it is often more efficient to put all the information in a single table. Some database designers take advantage of this relationship by creating tables that contain a subset of the data from another table.
  • One-to-many relationships are the most common type of database relationship. They occur when each record in Table A corresponds to one or more records in Table B, but each record in Table B corresponds to only one record in Table A. For example, the relationship between a Teachers table and a Students table in an elementary school database would likely be a one-to-many relationship because each student has only one teacher, but each teacher has several students. This one-to-many design helps eliminate duplicated data.
  • Many-to-many relationships occur when each record in Table A corresponds to one or more records in Table B, and each record in Table B corresponds to one or more records in Table A. For example, the relationship between a Teachers and a Courses table would likely be many-to-many because each teacher may instruct more than one course, and each course may have more than one instructor.

Self-Referencing Relationships: A Special Case

Self-referencing relationships occur when there is only one table involved. One common example is an Employees table that contains information about the supervisor of each employee. Each supervisor is also an employee and has a supervisor. In this case, there is a one-to-many self-referencing relationship, as each employee has one supervisor, but each supervisor may have more than one employee.

Creating Relationships With Foreign Keys

You create relationships between tables by specifying a foreign key. This key tells the relational database how the tables are related. In many cases, a column in Table A contains primary keys that are referenced from Table B.

Consider again the example of the Teachers and Students tables. The Teachers table contains just an ID, a name, and a course column:






John Doe


Jane Schmoe


The Students table includes an ID, name, and a foreign key column:





Lowell Smith


Brian Short


Corky Mendez


Monica Jones


The column Teacher_FK in the Students table references the primary key value of an instructor in the Teachers table. Frequently, database designers use "PK" or "FK" in the column name to easily identify a primary key or foreign key column.

These two tables illustrate a one-to-many relationship between the teachers and the students.

Relationships and Referential Integrity

After you've added a foreign key to a table, create a database constraint that enforces referential integrity between the two tables. This step ensures that relationships between tables remain consistent. When one table has a foreign key to another table, referential integrity requires that any foreign key value in Table B must refer to an existing record in Table A. 

Implementing Relationships

Depending on your database, you implement relationships between tables in different ways. Microsoft Access provides a wizard that allows you to link tables and also enforce referential integrity. 

If you are writing SQL directly, you would first create the table Teachers, declaring an ID column to be the primary key:

Teacher_Name VARCHAR(100),
Course VARCHAR(100)

When you create the Students table, you declare the Teacher_FK column to be a foreign key referencing the InstructorID column in the Teachers' table:

Student_Name VARCHAR(100), Teacher_FK INT,
FOREIGN KEY (Teacher_FK) REFERENCES Teachers(InstructorID) )

Using Relationships to Join Tables

After you've created one or more relationships in your database, leverage their power by using SQL JOIN queries to combine information from multiple tables. The most common type of join is a SQL INNER JOIN, which is a simple join. This type of join returns all records that meet the join condition from one or more tables. For example, this JOIN condition returns Student_Name, Teacher_Name, and Course, where the foreign key in the Students table matches the primary key in the Teachers table:

SELECT Students.Student_Name, Teachers.Teacher_Name, Teachers.Course
FROM Students
ON Students.Teacher_FK=Teachers.InstructorID;

This statement produces a table something like this:

Returned Table from the SQL Join Statement

Student_Name Teacher_Name Course
Lowell Smith John Doe English
Brian Short John Doe English
Corky Mendez Jane Schmoe Math
Monica Jones John Doe English
Was this page helpful?