Introduction to Database Relationships

Relational databases support enforced, defined links between tables

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

The database terms relational or relationship describes 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, more common in massive big-data initiatives, insofar as 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.

Types of Database Relationships

There are three different types of database relationships, each named according to the number of table rows that may be 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 simply put all of the information in a single table. Some database designers do 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 his or her own 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 will 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 easily allows you to link tables and to 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:

    CREATE TABLE Students (
    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, or 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 will return 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
    INNER JOIN Teachers
    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