Table relationships in Supabase - Step-by-Step Execution

Process Flow - Table relationships

Create Table A

↓

Create Table B

↓

Define Foreign Key in B referencing A

↓

Insert Data in A

↓

Insert Data in B with reference to A

↓

Query Join: B JOIN A on Foreign Key

↓

Result: Combined Data from A and B

This flow shows how two tables are created, linked by a foreign key, and then queried together to combine related data.

Execution Sample

Supabase

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

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

Creates two tables: authors and books, where books.author_id links to authors.id as a foreign key.

Process Table

Step	Action	Table A (authors)	Table B (books)	Result/Note
1	Create authors table	Empty table created with columns id, name	-	Table authors ready
2	Create books table with foreign key	-	Empty table created with columns id, title, author_id (FK to authors.id)	Table books ready with FK constraint
3	Insert author: id=1, name='Alice'	Row inserted: {id:1, name:'Alice'}	-	Author Alice added
4	Insert book: id=1, title='Book A', author_id=1	-	Row inserted: {id:1, title:'Book A', author_id:1}	Book linked to Alice
5	Insert book: id=2, title='Book B', author_id=1	-	Row inserted: {id:2, title:'Book B', author_id:1}	Another book linked to Alice
6	Query join books JOIN authors ON books.author_id = authors.id	authors: {id:1, name:'Alice'}	books: {id:1, title:'Book A', author_id:1}, {id:2, title:'Book B', author_id:1}	Returns combined rows with book and author info
7	Insert book with invalid author_id=99	-	Insert fails	Foreign key constraint prevents invalid reference

💡 Foreign key constraint stops invalid data insertion; join query returns combined related data.

Status Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	After Step 6	Final
authors	empty	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]
books	empty	empty	[{id:1, title:'Book A', author_id:1}]	[{id:1, title:'Book A', author_id:1}, {id:2, title:'Book B', author_id:1}]	[{id:1, title:'Book A', author_id:1}, {id:2, title:'Book B', author_id:1}]	[{id:1, title:'Book A', author_id:1}, {id:2, title:'Book B', author_id:1}]

Key Moments - 2 Insights

Why can't we insert a book with an author_id that doesn't exist in authors?

Because the foreign key constraint (see step 7) ensures books.author_id must match an existing authors.id. This keeps data linked correctly.

How does the join query combine data from two tables?

At step 6, the join matches books.author_id with authors.id, combining rows where these values are equal, showing book info with its author.

Visual Quiz - 3 Questions

Look at the execution table at step 4, what data is inserted into the books table?

A {id:1, title:'Book A', author_id:1}

B {id:1, title:'Book A', author_id:2}

C {id:2, title:'Book B', author_id:1}

D No data inserted

💡 Hint

Check the 'Table B (books)' column at step 4 in the execution table.

At which step does the foreign key constraint prevent an invalid insert?

A Step 5

B Step 6

C Step 7

D Step 3

💡 Hint

Look for the step where insertion fails due to foreign key in the execution table.

If we add a new author with id=2, how would the variable 'authors' change after step 3?

A It would remain the same

B It would include two rows with ids 1 and 2

C It would remove the existing author

D It would cause an error

💡 Hint

Refer to the variable_tracker for 'authors' after step 3 and imagine adding another row.

Concept Snapshot

Table relationships link data across tables using keys.
Create tables with primary keys and foreign keys.
Foreign key in one table points to primary key in another.
Insert data respecting these keys to keep links valid.
Use JOIN queries to combine related data from both tables.

Full Transcript

This lesson shows how to create two tables in Supabase, authors and books, where books has a foreign key linking to authors. We create authors first, then books with a foreign key constraint. We insert an author and then books linked to that author. The foreign key prevents inserting books with invalid author references. Finally, a join query combines books and authors data. The execution table traces each step, showing table states and actions. Key moments clarify why foreign keys matter and how joins work. The quiz tests understanding of data insertion, constraints, and variable changes.

Practice

1. What is the main purpose of creating table relationships in Supabase?

easy

A. To store files in the database

B. To connect data across tables using keys

C. To increase the size of the database

D. To change the database theme

Solution

1. Step 1: Understand table relationships

   Table relationships link data in different tables using keys to keep data connected.

2. Step 2: Identify the purpose

   This linking helps to find related data easily, like joining customer info with orders.

3. Final Answer:

   To connect data across tables using keys -> Option B

4. Quick Check:

   Table relationships = Connect data [OK]

Hint: Relationships link tables by keys for easy data access [OK]

Common Mistakes:

• Thinking relationships store files
• Believing relationships increase database size
• Confusing relationships with UI themes

2. Which SQL keyword is used in Supabase to create a foreign key relationship between tables?

easy

A. REFERENCES

B. JOIN

C. SELECT

D. INSERT

Solution

1. Step 1: Identify the keyword for foreign keys

   In SQL, the REFERENCES keyword defines a foreign key that links one table's column to another's primary key.

2. Step 2: Match with Supabase usage

   Supabase uses standard SQL, so REFERENCES is used to create safe links between tables.

3. Final Answer:

   REFERENCES -> Option A

4. Quick Check:

   Foreign key = REFERENCES [OK]

Hint: Use REFERENCES to link tables with foreign keys [OK]

Common Mistakes:

• Using JOIN to create relationships (JOIN reads data, doesn't create keys)
• Confusing SELECT or INSERT with key creation

3. Given these two tables:
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 will this query return?
SELECT books.title, authors.name FROM books JOIN authors ON books.author_id = authors.id;

medium

A. A list of books without author names

B. A list of authors without book titles

C. An error because of missing foreign key

D. A list of book titles with their author's names

Solution

1. Step 1: Understand the table relationship

   The books table has a foreign key author_id linking to authors.id, connecting each book to its author.

2. Step 2: Analyze the JOIN query

   The query joins books and authors on matching author_id and id, selecting book titles and author names together.

3. Final Answer:

   A list of book titles with their author's names -> Option D

4. Quick Check:

   JOIN on foreign key returns related data [OK]

Hint: JOIN returns combined rows matching foreign keys [OK]

Common Mistakes:

• Expecting authors without books from this JOIN
• Thinking query causes error due to foreign key
• Assuming books without author names appear

4. You wrote this table creation:
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, FOREIGN KEY customer_id REFERENCES customers(id));
Why does this cause an error in Supabase?

medium

A. FOREIGN KEY syntax is incorrect; it needs parentheses around the column

B. customer_id must be TEXT, not INT

C. PRIMARY KEY cannot be SERIAL

D. REFERENCES keyword is not supported

Solution

1. Step 1: Check FOREIGN KEY syntax

   The FOREIGN KEY clause requires parentheses around the column name, like FOREIGN KEY (customer_id).

2. Step 2: Confirm other parts

   customer_id as INT is valid, SERIAL primary key is valid, and REFERENCES is supported in Supabase.

3. Final Answer:

   FOREIGN KEY syntax is incorrect; it needs parentheses around the column -> Option A

4. Quick Check:

   FOREIGN KEY syntax = parentheses needed [OK]

Hint: Put column names in parentheses after FOREIGN KEY [OK]

Common Mistakes:

• Omitting parentheses around foreign key column
• Changing data type unnecessarily
• Thinking REFERENCES is unsupported

5. You want to create a many-to-many relationship between students and courses tables in Supabase. Which setup is best?

hard

A. Add a foreign key column in students pointing to courses

B. Add a foreign key column in courses pointing to students

C. Create a join table with student_id and course_id as foreign keys

D. Merge students and courses into one table

Solution

1. Step 1: Understand many-to-many relationships

   Many-to-many means each student can take many courses and each course can have many students.

2. Step 2: Choose the correct design

   Neither students nor courses alone can hold all links. A join table with student_id and course_id foreign keys connects them properly.

3. Final Answer:

   Create a join table with student_id and course_id as foreign keys -> Option C

4. Quick Check:

   Many-to-many = join table with two foreign keys [OK]

Hint: Use a join table for many-to-many links [OK]

Common Mistakes:

• Trying to store many-to-many in one table column
• Merging unrelated tables
• Using one foreign key only