Foreign Keys: FOREIGN KEY

Imagine you have two tables: one with data about students and another with data about courses. How do you think you could show which students are enrolled in which courses? You could add a column to the students table to store all the courses they're enrolled in, but that would get messy real quick. One of the best solutions is to use foreign keys!

A foreign key (FOREIGN KEY) is a column or a set of columns used to create a link between tables. It points to a column (usually a PRIMARY KEY) in another table, making sure the data between them stays consistent. So if a student is enrolled in a course, we can be sure that course actually exists.

Foreign keys:

• Help keep your data consistent by making sure records in one table match up with data in another.
• Make your database clear and logically organized.
• Make working with data easier, prevent duplicates and mistakes.

Real-life Example

Picture a library. The books table has a list of books, and the members table is a list of readers. To keep track of who borrowed which book, we can create a borrowed_books table that references books and members using foreign keys. That way, every time someone borrows a book, we know exactly who it was and which book they took.

Types of Relationships Between Tables

Foreign keys let you describe different kinds of relationships between tables. Let's break down the three main types:

1. One-to-One Relationship (ONE-TO-ONE)

This is the simplest relationship, where one record in one table matches exactly one record in another table. For example, a users table might have info about users, and a profiles table could have extra info like their addresses or photos.

SQL example for a ONE-TO-ONE relationship:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE TABLE profiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(user_id), -- refers to users.user_id
    address TEXT
);

Here, the user_id column in the profiles table acts as a foreign key pointing to user_id in the users table.

Example users table:

Example profiles table:

2. One-to-Many Relationship (ONE-TO-MANY)

This is the most popular relationship. For example, one customer can make lots of orders. In this case, the customers table is linked to the orders table using a foreign key.

SQL example for a ONE-TO-MANY relationship:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),	-- refers to customers.customer_id
    order_date DATE NOT NULL
);

The orders table can have lots of records pointing to a single record in the customers table.

Example customers table:

Example orders table:

3. Many-to-Many Relationship (MANY-TO-MANY)

Sometimes, one record in a table can be linked to several records in another table, and vice versa. For example, students enroll in courses, and a course can have lots of students. For this, you usually create a join table.

SQL example for a MANY-TO-MANY relationship:

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

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

-- "enrollment of a specific student in a specific course"
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id), -- reference to student id
    course_id INT REFERENCES courses(course_id),	-- reference to course id
    PRIMARY KEY (student_id, course_id)
);

Here, the enrollments table acts as a "bridge" between the students and courses tables.

Example students table:

Example courses table:

Example enrollments table:

Benefits of Using Foreign Keys

1. Your data stays consistent. If a foreign key points to a record that doesn't exist, PostgreSQL won't let you insert or update the data.
2. Working with tables gets easier. Foreign keys make it simple to link data between tables and run complex queries like JOIN.
3. Automatic behavior. You can set up what happens when related records are deleted or updated (like cascading deletes, setting to NULL, etc.).

What Does a Foreign Key Look Like in PostgreSQL?

Here's the syntax for creating a foreign key when making a table:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)   -- foreign key reference
);

Here:

• customer_id is the foreign key.
• It points to customer_id in the customers table.

You can also explicitly specify a foreign key using FOREIGN KEY:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Fun Fact

The term "foreign key" (FOREIGN KEY) comes from relational database theory, developed in the 1970s by Edgar Codd. It's wild, but the main idea is so powerful that it's still a key part of modern databases. Who said classics aren't relevant?

Now you get what a foreign key is and why it's so important. You'll still need to learn how to create them when making tables and how to use cascading operations the right way. But that's for the next lectures!