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.