Table relationships in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Foreign Key Constraints in Supabase

In Supabase, you want to link two tables: orders and customers. Each order must belong to one customer. Which of the following best describes the role of a foreign key in this relationship?

A A foreign key in <code>orders</code> points to a non-unique column in <code>customers</code>, allowing multiple customers per order.

B A foreign key in <code>customers</code> points to the primary key in <code>orders</code>, ensuring each customer has an order.

C A foreign key in <code>orders</code> points to the primary key in <code>customers</code>, ensuring each order is linked to a valid customer.

D A foreign key in <code>customers</code> points to a foreign key in <code>orders</code>, creating a circular reference.

💡 Hint

Think about which table holds the reference to the other to enforce the link.

The foreign key is placed in the orders table pointing to the primary key in customers. This ensures each order is linked to exactly one existing customer.

❓ Configuration

intermediate

Configuring One-to-Many Relationship in Supabase

You have two tables: authors and books. Each author can write many books. Which SQL snippet correctly creates a foreign key in books to link to authors?

A ALTER TABLE books ADD COLUMN author_id UUID REFERENCES authors(id);

B ALTER TABLE authors ADD COLUMN book_id UUID REFERENCES books(id);

C ALTER TABLE books ADD COLUMN author_id UUID UNIQUE REFERENCES authors(id);

D ALTER TABLE books ADD COLUMN author_id UUID NOT NULL;

💡 Hint

The foreign key should be in the table that holds many records.

Since one author can have many books, the books table should have a foreign key author_id referencing authors(id). Option A correctly adds this foreign key.

❓ Architecture

advanced

Designing Many-to-Many Relationships in Supabase

You want to model a many-to-many relationship between students and courses in Supabase. Which architecture is the best practice?

A Add a foreign key column in <code>courses</code> pointing to <code>students</code>.

B Create a join table <code>enrollments</code> with foreign keys to both <code>students</code> and <code>courses</code>.

C Add a foreign key column in <code>students</code> pointing to <code>courses</code>.

D Store course IDs as a list inside a column in <code>students</code>.

💡 Hint

Many-to-many relationships usually require a separate table to link records.

Many-to-many relationships are best handled by a join table that holds foreign keys to both related tables. This avoids data duplication and maintains integrity.

❓ security

advanced

Securing Table Relationships with Row Level Security (RLS)

In Supabase, you want to ensure users can only see their own orders. The orders table has a foreign key user_id referencing users. Which RLS policy correctly enforces this?

A CREATE POLICY "Users can view their orders" ON orders FOR SELECT USING (user_id = auth.uid());

B CREATE POLICY "Users can view all orders" ON orders FOR SELECT USING (true);

C CREATE POLICY "Users can view orders" ON orders FOR SELECT USING (user_id != auth.uid());

D CREATE POLICY "Users can view orders" ON orders FOR SELECT USING (user_id IS NULL);

💡 Hint

Check the condition that matches the current user's ID with the order's user_id.

The policy in option A allows users to select only orders where the user_id matches their authenticated user ID, enforcing row-level security.

❓ service_behavior

expert

Behavior of Cascading Deletes in Supabase Table Relationships

You have two tables: projects and tasks. tasks has a foreign key project_id referencing projects with ON DELETE CASCADE. What happens when you delete a project?

A The delete fails if there are tasks linked to the project.

B Only the project is deleted; tasks remain unchanged.

C The tasks remain but their <code>project_id</code> is set to NULL.

D All tasks linked to the deleted project are automatically deleted.

💡 Hint

Consider what ON DELETE CASCADE means for related records.

The ON DELETE CASCADE option causes all related tasks to be deleted automatically when the parent project is deleted, maintaining referential integrity.

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