Creating Foreign Keys When Creating Tables

Welcome to one of the key lectures in our course! Today we’re gonna talk about how to create foreign keys in PostgreSQL. This topic is super important in database design, because foreign keys are what let you set up relationships between tables. If you feel like you might get lost in your future "SQL city," just imagine foreign keys as bridges connecting different neighborhoods.

To put it simply, a foreign key is a column (or a set of columns) in one table that references a column (usually a primary key) in another table.

For example, if you have two tables — students and courses, a foreign key in the courses table can "point" to which student is enrolled in a course. That’s how you create a link between these tables.

Why does this matter?

1. A foreign key helps guarantee data integrity: you can’t write something in one table if it doesn’t exist in the other.
2. They make working with data easier. For example, when you delete a record in one table, you can set up automatic deletion of related records in another.

Foreign Key Creation Syntax

Creating a foreign key in PostgreSQL is easy — just a bit of SQL magic. Here’s the basic syntax:

CREATE TABLE dependent_table (
    column_foreign_id DATA_TYPE REFERENCES parent_table(column_id)
);

Let’s dive into the details and check out a couple of examples.

Example 1: students and courses Tables

Imagine we want to create a link between students and courses. Each course is tied to a single student. Here’s all you need to do:

Note: this is an artificially simplified model meant to illustrate the FOREIGN KEY syntax. In real life the relationship between students and courses is many-to-many, and we’ll model it properly below with an enrollments table.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id)
);

Here:

• In the students table, we created a primary key PRIMARY KEY to identify each student.
• In the courses table, the student_id column is a foreign key FOREIGN KEY that references the student_id column in the students table.

students Table

courses Table

Important Note

When you add a foreign key, PostgreSQL automatically creates a rule that checks if the values in the foreign key match existing values in the referenced table. If you try to insert an invalid value, the database will throw an error.

Practical Use: students, courses, and enrollments Model

Let’s look at a more complex example with a "many-to-many" relationship. The same students can enroll in several courses, and one course can have lots of students. To create this kind of link, we need a join table.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id), -- foreign key
    course_id INT REFERENCES courses(course_id)		-- foreign key
);

Here, the enrollments table links the students and courses tables using the foreign keys student_id and course_id.

Inserting Data

-- Adding students
INSERT INTO students (name) VALUES ('Ivan Ivanov'), ('Maria Smirnova');

-- Adding courses
INSERT INTO courses (title) VALUES ('Mathematics'), ('Physics');

-- Enrolling students in courses
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

Selecting Data

Now we can easily run queries to find out which courses a student is taking or which students are enrolled in a specific course:

-- Courses Ivan Ivanov is enrolled in
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- Students enrolled in the "Mathematics" course
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

Advanced Features: ON DELETE and ON UPDATE

A foreign key also needs to control what happens when you change or delete records in the parent table. That’s what the ON DELETE and ON UPDATE modifiers are for. Here are the main options:

• CASCADE: changes or deletions in the parent table automatically apply to child records.
• SET NULL: the foreign key values in the child table are set to NULL.
• RESTRICT: prevents deleting or changing data if it’s already used in the dependent child table.
• NO ACTION: basically the same as RESTRICT, but the check happens later.

Example 2: Using ON DELETE CASCADE

Imagine we want to make it so that deleting a student from the students table automatically deletes all their courses from the courses table. Here’s how you do it:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE
);

Now, if you delete a student from the students table, all records linked to that student in the courses table will also be deleted. For example:

INSERT INTO students (name) VALUES ('Ivan Ivanov');
INSERT INTO courses (title, student_id) VALUES ('Mathematics', 1), ('Physics', 1);

-- Deleting student Ivanov
DELETE FROM students WHERE student_id = 1;

-- The courses table will now be empty, since all courses linked to Ivanov are deleted

We’ll go over this topic again and in more detail in the next lecture.

Data Validation Process When Using Foreign Keys

When you create a foreign key, PostgreSQL acts like a strict gatekeeper, checking every new record. For example:

• If you try to insert a record with a non-existent foreign key value, you’ll get an error.
• If you delete a record in the parent table that’s linked to other tables (without ON DELETE CASCADE), it’ll cause a data integrity violation.

Example: Trying to Insert Invalid Data

-- Trying to enroll a course for a non-existent student
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- Error: foreign key constraint violation

Common Mistakes When Creating Foreign Keys

1. No index on the foreign key. PostgreSQL automatically creates an index for the primary key, but not for the foreign key. If you’re gonna use the foreign key a lot in WHERE conditions, you should create an index manually.
2. Wrong order of table creation. You can’t create a foreign key that references a table that doesn’t exist yet.
3. Forgot the ON DELETE or ON UPDATE modifier. This can lead to unexpected behavior when editing data.

Now that you know how to create foreign keys, you’ve got a powerful tool for building structured and consistent databases. In the next lecture, we’ll dig deeper into ON DELETE CASCADE and ON UPDATE RESTRICT actions for managing related data.