Primary keys and foreign keys in Supabase - Mini Project: Build & Apply

Primary keys and foreign keys

📖 Scenario: You are setting up a simple database for a library system using Supabase. You need to create tables to store information about authors and their books. Each author can have multiple books, so you will use primary keys and foreign keys to link the tables properly.

🎯 Goal: Create two tables in Supabase: authors and books. Define a primary key for each table and a foreign key in books that references the authors table to connect each book to its author.

📋 What You'll Learn

Create a table called authors with columns id (primary key) and name (text).

Create a table called books with columns id (primary key), title (text), and author_id (foreign key referencing authors.id).

Use SQL commands compatible with Supabase to define primary keys and foreign keys.

💡 Why This Matters

🌍 Real World

Databases often need to link related data, like authors and their books, using keys to keep data organized and connected.

💼 Career

Understanding primary and foreign keys is essential for database design, a key skill for backend developers and cloud engineers.

1

Create the authors table with a primary key

Write a SQL command to create a table called authors with two columns: id as an integer primary key and name as text.

Supabase

-- Your SQL command to create the authors table
-- Include columns id (primary key) and name (text)

Need a hint?

Use SERIAL PRIMARY KEY for the id column to auto-increment.

2

Create the books table with a primary key

Write a SQL command to create a table called books with three columns: id as an integer primary key, title as text, and author_id as an integer.

Supabase

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- Your SQL command to create the books table
-- Include columns id (primary key), title (text), and author_id (integer)

Need a hint?

Define author_id as an integer for now; foreign key will be added next.

3

Add a foreign key constraint to books.author_id

Modify the books table creation SQL to add a foreign key constraint on author_id that references authors.id.

Supabase

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- Modify the books table creation below to add a foreign key constraint on author_id
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author_id INTEGER
    -- Add foreign key constraint here
);

Need a hint?

Use REFERENCES authors(id) after author_id INTEGER to set the foreign key.

4

Complete the database schema with primary and foreign keys

Ensure the full SQL commands for creating authors and books tables include the primary keys and the foreign key constraint on books.author_id referencing authors.id.

Supabase

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- Confirm the schema is complete

Need a hint?

Check that both tables have primary keys and the foreign key is correctly set.