Link Relational Tables Using Primary and Foreign Keys

R2026b
Open Live Script

This example shows how to link database tables to combine related data, such as associating orders with customers. Relational databases use primary keys to uniquely identify records and foreign keys to reference records across tables. In this example, you use both key types to join tables containing students, courses, and enrollment data.

Connect to Database

Use the duckdb function to connect to a transient in-memory DuckDB™ database.

conn = duckdb;

Create Database Tables

Create a table to store student information using the execute function with the following SQL statement. The Students table contains columns StudentID, Name, and Age, with StudentID as the primary key.

createStudentsTable = "CREATE TABLE Students (StudentID INTEGER PRIMARY KEY, Name VARCHAR, Age INTEGER)";
execute(conn,createStudentsTable)

Use the execute function to create a table that stores course enrollment information. The Courses table contains columns CourseID, Title, and Credits, with CourseID as the primary key.

createCoursesTable = "CREATE TABLE Courses (CourseID INTEGER PRIMARY KEY, Title VARCHAR, Credits INTEGER)";
execute(conn,createCoursesTable)

Create a table to capture student enrollment information using the execute function. The Enrollments table contains columns EnrollmentID, StudentID, CourseID, and Grade, with EnrollmentID as the primary key. This table also has two foreign keys:

  • StudentID linking to Students.StudentID

  • CourseID linking to Courses.CourseID

createEnrollmentsTable = "CREATE TABLE Enrollments (EnrollmentID INTEGER PRIMARY KEY, StudentID INTEGER, CourseID INTEGER, Grade INTEGER, FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses(CourseID))";
execute(conn,createEnrollmentsTable)

Populate the tables with student, course, and enrollment values.

execute(conn,"INSERT INTO Students VALUES (1,'Alice',20),(2,'Bob',22)");
execute(conn,"INSERT INTO Courses VALUES (101,'Math',4),(102,'History',3)");
execute(conn,"INSERT INTO Enrollments VALUES (1001,1,101,95),(1002,2,102,88)");

Retrieve each table using the fetch function to verify the values.

data = fetch(conn,'SELECT * FROM Students;');
disp(data)
    StudentID     Name      Age
    _________    _______    ___

        1        "Alice"    20 
        2        "Bob"      22 

data = fetch(conn,'SELECT * FROM Courses;');
disp(data)
    CourseID      Title      Credits
    ________    _________    _______

      101       "Math"          4   
      102       "History"       3   

data = fetch(conn,'SELECT * FROM Enrollments;');
disp(data)
    EnrollmentID    StudentID    CourseID    Grade
    ____________    _________    ________    _____

        1001            1          101        95  
        1002            2          102        88

Join Database Tables

Use the following SQL query with the fetch function to return a table containing student names, enrolled courses, and grades.

sqlQuery = [...
    "SELECT Students.Name, Courses.Title, Enrollments.Grade " +... 
    "FROM Enrollments " + ...
    "JOIN Students ON Enrollments.StudentID = Students.StudentID " +...
    "JOIN Courses ON Enrollments.CourseID = Courses.CourseID"...
    ];
fetch(conn,sqlQuery)
ans = 2×3 table
     Name        Title      Grade
    _______    _________    _____

    "Alice"    "Math"        95  
    "Bob"      "History"     88

Close the database connection.

close(conn)

See Also

Functions

Topics