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.

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