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.