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.

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