Table relationships in Supabase - Mini Project: Build & Apply

PMC: Table relationships

📖 Scenario: You are building a simple online store database using Supabase. You want to connect two tables: customers and orders. Each order belongs to one customer. This relationship helps you find all orders made by a customer.

🎯 Goal: Create two tables in Supabase: customers and orders. Then, set up a foreign key relationship from orders.customer_id to customers.id to link orders to customers.

📋 What You'll Learn

Create a customers table with columns id (primary key) and name (text).

Create an orders table with columns id (primary key), customer_id (foreign key), and product (text).

Add a foreign key constraint on orders.customer_id referencing customers.id.

Use SQL commands valid in Supabase/PostgreSQL.

💡 Why This Matters

🌍 Real World

Table relationships are essential in databases to connect related data, like customers and their orders in an online store.

💼 Career

Understanding how to create and use foreign keys is a key skill for database administrators and backend developers working with cloud databases like Supabase.

1

Create the customers table

Write a SQL command to create a table called customers with two columns: id as an integer primary key and name as text.

Supabase

-- Your SQL command here to create the customers table

Need a hint?

Use SERIAL PRIMARY KEY for auto-incrementing id.

2

Create the orders table

Write a SQL command to create a table called orders with three columns: id as an integer primary key, customer_id as integer, and product as text.

Supabase

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT
);
-- Your SQL command here to create the orders table

Need a hint?

Define customer_id as INTEGER to link to customers.id.

3

Add foreign key constraint to orders

Write a SQL command to alter the orders table by adding a foreign key constraint on customer_id that references customers.id.

Supabase

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product TEXT
);
-- Your SQL command here to add foreign key constraint

Need a hint?

Use ALTER TABLE and ADD CONSTRAINT to add the foreign key.

4

Verify the table relationship

Write a SQL query to select all columns from orders joined with customers on the foreign key customer_id to show orders with customer names.

Supabase

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product TEXT
);

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Your SQL query here to join orders and customers

Need a hint?

Use JOIN with ON orders.customer_id = customers.id to combine tables.