Table relationships in Supabase

Introduction

Table relationships help connect data in different tables so you can find related information easily.

You want to link customers to their orders in a shopping app.

You need to connect blog posts to their authors.

You want to track which products belong to which categories.

You want to see all comments related to a specific post.

You want to organize data so it is easy to update and query.

Syntax

Supabase

CREATE TABLE child_table (
  id SERIAL PRIMARY KEY,
  parent_id INTEGER REFERENCES parent_table(id)
);

The REFERENCES keyword creates a link from one table to another using a column.

The linked column usually is a primary key in the parent table.

Examples

This example links posts to authors using author_id.

Supabase

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER REFERENCES authors(id),
  title TEXT NOT NULL
);

Products are connected to categories by category_id.

Supabase

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id),
  product_name TEXT NOT NULL
);

Sample Program

This creates two tables: customers and orders. Orders link to customers using customer_id. Then it inserts sample data and shows how to get orders with customer names.

Supabase

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  order_date DATE NOT NULL
);

-- Insert sample data
INSERT INTO customers (name) VALUES ('Alice'), ('Bob');
INSERT INTO orders (customer_id, order_date) VALUES (1, '2024-06-01'), (2, '2024-06-02');

-- Query orders with customer names
SELECT orders.id, customers.name, orders.order_date
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Important Notes

Always use primary keys in parent tables for reliable links.

Use JOIN queries to get related data from connected tables.

Relationships help keep data organized and avoid duplicates.

Summary

Table relationships connect data across tables using keys.

They make it easy to find related information with JOINs.

Use REFERENCES to create these links safely and clearly.