Primary keys and foreign keys in Supabase - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a primary key in a database?

A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same value in this column, helping to find data quickly and avoid duplicates.

beginner

What is a foreign key in a database?

A foreign key is a column in one table that links to the primary key in another table. It helps connect related data across tables, like linking a customer to their orders.

intermediate

Why are primary keys important in Supabase tables?

Primary keys in Supabase ensure each row is unique and can be referenced by other tables. This helps keep data organized and supports relationships between tables.

intermediate

How does a foreign key enforce data integrity?

A foreign key makes sure that the value in one table matches a valid primary key in another table. This prevents invalid or orphaned data, keeping the database consistent.

intermediate

Can a table have more than one foreign key? Explain.

Yes, a table can have multiple foreign keys. Each foreign key links to a different table, allowing complex relationships like an order linked to both a customer and a product.

What does a primary key guarantee in a table?

A Each row is unique

B All columns are unique

C Data is encrypted

D Table has no rows

A primary key guarantees that each row in the table has a unique value in that column.

What is the role of a foreign key?

A To uniquely identify rows

B To speed up queries

C To store large files

D To link tables together

A foreign key links one table to another by referencing the primary key.

In Supabase, what happens if you try to insert a foreign key value that does not exist in the referenced table?

A The insert succeeds

B The foreign key is ignored

C The insert fails with an error

D The referenced table is updated

Foreign key constraints prevent inserting invalid references, so the insert fails.

Can a primary key column contain NULL values?

A No, primary keys must be NOT NULL

B Only if the table is empty

C Yes, always

D Only in foreign key columns

Primary keys must have a value for every row; NULL is not allowed.

Which of these best describes a composite primary key?

A A foreign key referencing multiple tables

B A primary key made of multiple columns

C A key that encrypts data

D A key that allows duplicates

A composite primary key uses more than one column to uniquely identify a row.

Explain in your own words what primary keys and foreign keys are and why they matter in a database.

Think about how you would organize a contact list and link it to messages.

Describe how foreign keys help keep data consistent when you have multiple tables in Supabase.

Imagine you have orders and customers in separate tables.

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