Primary keys and foreign keys in Supabase - Time & Space Complexity

Time Complexity: Primary keys and foreign keys

O(n)

Understanding Time Complexity

When working with primary keys and foreign keys in Supabase, it's important to understand how the number of database operations grows as data increases.

We want to know how the time to insert or query data changes when tables have these keys.

Scenario Under Consideration

Analyze the time complexity of inserting rows with primary and foreign keys.

// Insert a new user
const { data: user } = await supabase
  .from('users')
  .insert([{ id: 1, name: 'Alice' }])
  .select()

// Insert a new order linked to the user
const { data: order } = await supabase
  .from('orders')
  .insert([{ id: 101, user_id: 1, product: 'Book' }])
  .select()
    

This sequence inserts a user with a primary key and an order referencing that user via a foreign key.

Identify Repeating Operations

Look at the main database calls that happen repeatedly as data grows.

• Primary operation: Insert row into 'users' and 'orders' tables.
• How many times: Once per new user and once per new order.

How Execution Grows With Input

Each insert checks keys to keep data consistent. As you add more rows, each insert still does a quick check.

Input Size (n)	Approx. API Calls/Operations
10 users + 10 orders	20 insert operations
100 users + 100 orders	200 insert operations
1000 users + 1000 orders	2000 insert operations

Pattern observation: The number of operations grows directly with the number of rows inserted.

Final Time Complexity

Time Complexity: O(n)

This means the time to insert rows grows linearly with the number of rows you add.

Common Mistake

[X] Wrong: "Adding foreign keys makes inserts take much longer as data grows exponentially."

[OK] Correct: Foreign key checks are efficient and use indexes, so insert time grows linearly, not exponentially.

Interview Connect

Understanding how keys affect database operations shows you can design data that stays fast and reliable as it grows.

Self-Check

"What if we added an index on the foreign key column? How would that affect the time complexity of inserts and queries?"

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