Creating the students table and linking it to the courses table

Let’s take a minute and walk through what we’ve already learned. We’ll go step by step through everything that’s happening. There’s a lot coming up that’s built on this stuff, so it’s a good idea to really get what’s going on with every command you run.

Let’s go over our students and courses example one more time. From the very beginning. From creating the tables to breaking down what’s happening at each stage. Ready?

Now we’re going to create a students table to store info about students, and link it to the courses table using a foreign key. Along the way, you’ll see how foreign keys help us model relationships between entities.

Creating the students table

The students table will store info about students: their unique ID, name, and birth date. We’ll create it using the CREATE TABLE command.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY, -- Unique key for each record
    name TEXT NOT NULL,            -- Student name (required field)
    birth_date DATE                -- Student's birth date
);

What’s going on here?

1. student_id SERIAL PRIMARY KEY: This is a unique ID for each student. The SERIAL type creates an auto-incrementing field, and PRIMARY KEY makes sure the ID is unique for every row.
2. name TEXT NOT NULL: This is the student’s name. We put NOT NULL so you can’t add a record without a name.
3. birth_date DATE: This field stores the birth date. The DATE type helps us work with dates.

If the table was a person, student_id would be its passport, name is the obvious name, and birth_date is that thing we sometimes like to hide, but for the database, it’s important.

Creating the courses table

Now let’s make a table to store info about courses. It’ll have a course ID, a title, and a description.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,  -- Unique course ID
    title TEXT NOT NULL,           -- Course title (required field)
    description TEXT               -- Course description
);

What’s going on here?

1. course_id SERIAL PRIMARY KEY: Same idea, this is an auto-incrementing field that gives each course a unique ID.
2. title TEXT NOT NULL: This is the course title. We put NOT NULL because, let’s be real, every course needs a name.
3. description TEXT: This is a short description of the course. It’s optional (no NOT NULL restriction).

A course without a title is like a book with no cover. But the database won’t let that happen!

Linking the students table to the courses table

Now let’s say each student can only be enrolled in one course. To express that, we add a foreign key to the students table that references the course ID from the courses table — each student row carries a single course_id:

ALTER TABLE students
ADD COLUMN course_id INT REFERENCES courses(course_id); -- Foreign key linking to courses table

What does course_id INT REFERENCES courses(course_id) do?

1. We add a course_id field on the student that points to a unique course ID (course_id) in the courses table.
2. The link between tables is set up with the REFERENCES keyword.
3. This means that every time you insert or update a row in students, the course_id value has to already exist in courses. If you try to point at a non-existent course_id, you’ll get an error.
4. If we had put the FK in courses(student_id) instead, it would mean the opposite: “one student can teach/own many courses.” Since we want “one course per student,” the FK belongs on the students side.

Inserting data into the tables

Once the tables are created, let’s add some courses first (because each student references a course), then the students.

Adding courses

INSERT INTO courses (title, description) VALUES
('SQL Basics', 'Learning basic SQL syntax'),
('Relational Databases', 'Understanding relational models'),
('PostgreSQL for Beginners', 'Installing and setting up PostgreSQL');

Adding students

INSERT INTO students (name, birth_date, course_id) VALUES
('Alex Lin',  '2000-05-10', 1),
('Maria Chi', '1998-02-15', 2),
('Otto Song', '2001-09-25', 3);

We specified the course_id for each student, linking them to the course in the courses table.

Checking the link between tables

Now let’s make sure our tables are actually connected. We’ll write a query that shows info about courses along with student names.

SELECT
    courses.title AS course_title,
    courses.description AS course_description,
    students.name AS student_name
FROM
    students
JOIN
    courses ON students.course_id = courses.course_id;

Sample result: Query result:

We linked courses to students using a foreign key — and now we can get related data in a single query. That’s how the relational model works!

Summary

I hope everything we just went through made sense and was clear for you. Because soon we’ll move on, and I want you to feel confident in your knowledge.

2

Task

SQL SELF, level 20, lesson 0

Locked

Creating the `students` table

Creating the `students` table

Open

2

Task

SQL SELF, level 20, lesson 0

Locked

Creating the `courses` table and a foreign key

Creating the `courses` table and a foreign key

Open