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)

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)

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
);

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

Hint

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

Practice

1. What is the main purpose of a primary key in a Supabase table?

easy

A. To store large text data

B. To link two tables together

C. To uniquely identify each row in the table

D. To speed up data insertion

Solution

1. Step 1: Understand the role of primary keys

   A primary key is used to uniquely identify each row in a table so no two rows have the same key.

2. Step 2: Differentiate from foreign keys

   Foreign keys link tables, but primary keys ensure uniqueness within one table.

3. Final Answer:

   To uniquely identify each row in the table -> Option C

4. Quick Check:

   Primary key = unique row ID [OK]

Hint: Primary key means unique row ID in one table [OK]

Common Mistakes:

• Confusing primary key with foreign key
• Thinking primary key speeds up insertion
• Assuming primary key stores large data

2. Which of the following is the correct way to define a primary key in a Supabase SQL table?

easy

A. CREATE TABLE users (id INT, PRIMARY KEY (id));

B. CREATE TABLE users (id INT, FOREIGN KEY (id));

C. CREATE TABLE users (id INT UNIQUE);

D. CREATE TABLE users (id INT NOT NULL);

Solution

1. Step 1: Review SQL syntax for primary keys

   The correct syntax uses PRIMARY KEY (column_name) to set the primary key.

2. Step 2: Check each option

   CREATE TABLE users (id INT, PRIMARY KEY (id)); uses correct syntax. CREATE TABLE users (id INT, FOREIGN KEY (id)); wrongly uses FOREIGN KEY. CREATE TABLE users (id INT UNIQUE); only makes column unique but not primary key. CREATE TABLE users (id INT NOT NULL); only sets NOT NULL.

3. Final Answer:

   CREATE TABLE users (id INT, PRIMARY KEY (id)); -> Option A

4. Quick Check:

   Primary key syntax = PRIMARY KEY (column) [OK]

Hint: Primary key syntax includes PRIMARY KEY keyword [OK]

Common Mistakes:

• Using FOREIGN KEY instead of PRIMARY KEY
• Using UNIQUE but missing PRIMARY KEY
• Assuming NOT NULL sets primary key

3. Given these two tables in Supabase:

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

What happens if you try to insert a book with author_id that does not exist in authors?

medium

A. The insert will fail due to foreign key constraint violation

B. The insert will succeed and create a book with invalid author_id

C. The insert will succeed but author_id will be set to NULL

D. The insert will delete the non-existing author automatically

Solution

1. Step 1: Understand foreign key constraints

   The author_id in books references authors(id), so it must match an existing author.

2. Step 2: Check behavior on invalid foreign key

   Inserting a book with a non-existing author_id violates the foreign key constraint, causing the insert to fail.

3. Final Answer:

   The insert will fail due to foreign key constraint violation -> Option A

4. Quick Check:

   Foreign key enforces valid references [OK]

Hint: Foreign key must match existing row or insert fails [OK]

Common Mistakes:

• Assuming insert succeeds with invalid foreign key
• Thinking foreign key sets NULL automatically
• Believing foreign key deletes referenced rows

4. You have this table definition in Supabase:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

When trying to insert an order with customer_id NULL, you get an error. What is the likely cause?

medium

A. Foreign key constraint requires matching non-NULL value unless specified

B. Primary key order_id must be NULL

C. Table must have a composite primary key

D. Foreign key column customer_id does not allow NULL

Solution

1. Step 1: Check NULLability of foreign key column

   Foreign key columns allow NULL unless the column definition includes NOT NULL.

2. Step 2: Identify the constraint causing error

   Here, customer_id INT NOT NULL prevents inserting NULL values.

3. Final Answer:

   Foreign key column customer_id does not allow NULL -> Option D

4. Quick Check:

   NOT NULL on FK column blocks NULL [OK]

Hint: Foreign key columns block NULL if NOT NULL specified [OK]

Common Mistakes:

• Assuming foreign key columns always allow NULL
• Confusing primary key NULL rules
• Thinking composite key is required here

5. You want to design two tables in Supabase: projects and tasks. Each task belongs to one project. Which setup correctly enforces this relationship with primary and foreign keys?

hard

A. CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT PRIMARY KEY);

B. CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT REFERENCES projects(id));

C. CREATE TABLE projects (id SERIAL, name TEXT);
CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT);

D. CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT UNIQUE);

Solution

1. Step 1: Define primary key on projects

   The projects table must have a primary key to uniquely identify each project.

   CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
   CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT REFERENCES projects(id));

   and B do this correctly.

2. Step 2: Define foreign key on tasks

   Each task must reference a project via a foreign key.

   CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
   CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT REFERENCES projects(id));

   correctly uses project_id INT REFERENCES projects(id).

3. Step 3: Check other options

   CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
   CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT PRIMARY KEY);

   wrongly makes project_id a primary key in tasks, which is incorrect.

   CREATE TABLE projects (id SERIAL, name TEXT);
   CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT);

   misses primary key on projects and foreign key on tasks.

   CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
   CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT UNIQUE);

   uses UNIQUE on project_id which wrongly restricts tasks to one per project.

4. Final Answer:

   CREATE TABLE projects (id SERIAL PRIMARY KEY, name TEXT);
   CREATE TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, project_id INT REFERENCES projects(id));

   -> Option B

5. Quick Check:

   Primary key on projects + foreign key on tasks = correct [OK]

Hint: Project ID primary key, task project_id foreign key [OK]

Common Mistakes:

• Making foreign key a primary key in child table
• Missing foreign key constraint
• Using UNIQUE on foreign key wrongly