Primary keys and foreign keys in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Primary Keys in Supabase

Which statement best describes the role of a primary key in a Supabase table?

A It uniquely identifies each row in the table and cannot contain NULL values.

B It allows duplicate values and can be NULL in some rows.

C It is used to store large binary data like images or files.

D It automatically encrypts all data in the table for security.

💡 Hint

Think about what makes each row in a table unique and how the database enforces it.

A primary key uniquely identifies each row and must have a value (not NULL). It helps keep data organized and accessible.

❓ Architecture

intermediate

Foreign Key Relationship in Supabase

In Supabase, you have two tables: users and orders. Which foreign key setup correctly links each order to a user?

Supabase

CREATE TABLE users (id UUID PRIMARY KEY, name TEXT);
CREATE TABLE orders (id UUID PRIMARY KEY, user_id UUID, order_date DATE);

A ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (order_date) REFERENCES users(id);

B ALTER TABLE users ADD CONSTRAINT fk_order FOREIGN KEY (id) REFERENCES orders(user_id);

C ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (id) REFERENCES users(user_id);

D ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

💡 Hint

Think about which table holds the reference to the other and which columns should match.

The orders table has a user_id column that references the id column in users. This creates a foreign key relationship linking orders to users.

❓ security

advanced

Impact of Missing Foreign Key Constraints

What is a likely consequence of not defining foreign key constraints between related tables in Supabase?

A The tables will merge automatically to avoid orphaned records.

B Data inconsistency may occur because related records can be deleted or changed without restrictions.

C The database will automatically delete all related records to maintain integrity.

D Supabase will prevent any inserts into the child table until the foreign key is defined.

💡 Hint

Consider what happens if the database does not enforce relationships between tables.

Without foreign key constraints, the database does not enforce relationships, so related data can become inconsistent or orphaned.

❓ service_behavior

advanced

Behavior of Primary Key Violation in Supabase

What happens if you try to insert a row into a Supabase table with a primary key value that already exists?

A The insert succeeds but the primary key is changed to a new unique value.

B The existing row is overwritten with the new data automatically.

C The insert fails with a unique constraint violation error, and the row is not added.

D The database creates a duplicate row with the same primary key value.

💡 Hint

Think about how databases enforce uniqueness for primary keys.

Primary keys must be unique. Attempting to insert a duplicate key causes an error and the insert is rejected.

✅ Best Practice

expert

Choosing Primary Keys for Performance and Scalability

Which primary key choice is best for a large Supabase table expected to scale with many inserts and queries?

A Use a UUID generated by the client for each row to ensure uniqueness and avoid collisions.

B Use an auto-incrementing integer primary key generated by the database for simplicity.

C Use a composite primary key made of multiple text columns to capture business logic.

D Use a random string of characters generated by the user to allow flexibility.

💡 Hint

Consider uniqueness, distribution, and avoiding bottlenecks in large-scale systems.

UUIDs generated by clients avoid collisions and allow distributed inserts without coordination, improving scalability and performance.