Database Relationships: An Introduction To Foreign Keys, Joins and E-R Diagrams

Format
mla apa chicago
Your Citation
Chapple, Mike. "Database Relationships: An Introduction To Foreign Keys, Joins and E-R Diagrams." ThoughtCo, Mar. 7, 2017, thoughtco.com/database-relationships-1019729. Chapple, Mike. (2017, March 7). Database Relationships: An Introduction To Foreign Keys, Joins and E-R Diagrams. Retrieved from https://www.thoughtco.com/database-relationships-1019729 Chapple, Mike. "Database Relationships: An Introduction To Foreign Keys, Joins and E-R Diagrams." ThoughtCo. https://www.thoughtco.com/database-relationships-1019729 (accessed September 20, 2017).
E-R Diagram
E-R Diagram.

The database term "relational" or "relationship" describes the way that data in tables is connected.  

Newcomers to the world of databases often have a hard time seeing the difference between a database and a spreadsheet. They see tables of data and recognize that databases allow you to organize and query data in new ways, but fail to grasp the significance of the relationships between data that give relational database technology its name.

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 the 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 multiple 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: 

    Teachers
    InstructorIDTeacher_NameCourse
    001John DoeEnglish
    002Jane SchmoeMath

     

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

    Students
    StudentIDStudent_NameTeacher_FK
    0200Lowell Smith001
    0201Brian Short001
    0202Corky Mendez002
    0203Monica Jones001

     

    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.

    Note that these two tables illustrate a one-to-many relationship between the teachers and the students.

    Relationships and Referential Integrity

    Once you’ve added a foreign key to a table, you can then create a database constraint that enforces referential integrity between the two tables. This ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states 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: 

    CREATE TABLE Teachers (

       InstructorID INT AUTO_INCREMENT 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 (
        StudentID INT AUTO_INCREMENT PRIMARY KEY,
        Student_Name VARCHAR(100), Teacher_FK INT,
        FOREIGN KEY (Teacher_FK) REFERENCES Teachers(InstructorID) )
    )
    ;

     

    Using Relationships to Join Tables

    Once you’ve created one or more relationships in your database, you can 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 multiple tables. For example, this JOIN condition will return the 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_NameTeacher_NameCourseLowell SmithJohn DoeEnglishBrian ShortJohn DoeEnglishCorky MendezJane SchmoeMathMonica JonesJohn DoeEnglish