Primary keys and foreign keys in Supabase - Step-by-Step Execution

Process Flow - Primary keys and foreign keys

Create Table with Primary Key

↓

Insert Unique Rows

↓

Create Related Table with Foreign Key

↓

Insert Rows with Foreign Key Values

↓

Database Checks Foreign Key Validity

↓

Allow or Reject Insert Based on Key Match

This flow shows how tables are created with primary keys and related tables use foreign keys to link data, ensuring data integrity.

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 an authors table with a primary key and a books table with a foreign key linking to authors.

Process Table

Step	Action	Table Affected	Key Type	Value Inserted	Check Result	Outcome
1	Create authors table	authors	Primary Key (id)	N/A	N/A	Table created with id as primary key
2	Insert author row	authors	Primary Key (id)	id=1, name='Alice'	Unique id=1	Row inserted
3	Create books table	books	Primary Key (id), Foreign Key (author_id)	N/A	N/A	Table created with foreign key to authors.id
4	Insert book row	books	Foreign Key (author_id)	id=1, title='Book A', author_id=1	author_id=1 exists in authors	Row inserted
5	Insert book row	books	Foreign Key (author_id)	id=2, title='Book B', author_id=2	author_id=2 NOT found in authors	Insert rejected - foreign key violation

💡 Foreign key check fails at step 5 because author_id=2 does not exist in authors table

Status Tracker

Variable	Start	After Step 2	After Step 4	After Step 5
authors table rows	empty	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]
books table rows	empty	empty	[{id:1, title:'Book A', author_id:1}]	[{id:1, title:'Book A', author_id:1}]
foreign key check	N/A	N/A	Passed for author_id=1	Failed for author_id=2

Key Moments - 3 Insights

Why does the insert fail when author_id=2 is used in books?

Because the foreign key requires author_id to exist in authors table. Step 5 shows the check fails since author_id=2 is not found.

Can primary keys have duplicate values?

No, primary keys must be unique. Step 2 shows insertion with id=1 succeeds because it's unique.

What happens if we try to insert a book without author_id?

If author_id is required (not nullable), insertion fails because foreign key must reference an existing author.

Visual Quiz - 3 Questions

Look at the execution table, what is the outcome at step 4 when inserting a book with author_id=1?

A Insert rejected due to foreign key violation

B Row inserted successfully

C Primary key conflict error

D Table creation error

💡 Hint

Check the 'Outcome' column for step 4 in the execution table

At which step does the foreign key check fail?

A Step 5

B Step 3

C Step 2

D Step 1

💡 Hint

Look at the 'Check Result' column for foreign key violations

If we insert author with id=2 before step 5, what would happen at step 5?

A Primary key conflict error

B Insert rejected due to foreign key violation

C Row inserted successfully

D Table creation error

💡 Hint

Refer to variable_tracker showing authors table rows and foreign key check

Concept Snapshot

Primary Key: Unique identifier for table rows, no duplicates allowed.
Foreign Key: Links to primary key in another table, enforces data integrity.
Insertions with foreign keys must match existing primary keys.
Violations cause insert failure to keep data consistent.

Full Transcript

This lesson shows how primary keys uniquely identify rows in a table, and foreign keys link rows between tables. We create an authors table with a primary key 'id'. Then we create a books table with a foreign key 'author_id' referencing authors.id. When inserting books, the database checks if author_id exists in authors. If it does, insertion succeeds; if not, insertion fails. This ensures data integrity by preventing orphaned references.