Relationships and Foreign Keys

---

The Relational Model

The "relational" in relational database refers to the relationships between tables. Understanding how to model these relationships is fundamental to effective database design.

---

Why Relationships Matter

Consider storing blog posts and their authors:

Without Relationships (Denormalized)

CREATE TABLE posts (
  id uuid PRIMARY KEY,
  title text,
  content text,
  author_name text,
  author_email text,
  author_bio text
);

Problems:

• Redundancy: Author info repeated for every post
• Inconsistency: Update author email in one place, forget others
• Wasted space: Same data stored multiple times
• Update anomalies: Which post has the "correct" author info?

With Relationships (Normalized)

CREATE TABLE users (
  id uuid PRIMARY KEY,
  name text,
  email text UNIQUE,
  bio text
);

CREATE TABLE posts (
  id uuid PRIMARY KEY,
  title text,
  content text,
  user_id uuid REFERENCES users(id)
);

Benefits:

• Single source of truth: Author info in one place
• Data integrity: Foreign keys enforce valid references
• Efficient updates: Change author once, reflected everywhere
• Flexible queries: Join as needed

---

Types of Relationships

One-to-Many (Most Common)

One user has many posts; each post belongs to one user.

-- One user...
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL
);

-- ...has many posts
CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  user_id uuid REFERENCES users(id) NOT NULL  -- Foreign key
);

users                    posts
┌─────┬───────────────┐  ┌─────┬─────────┬─────────┐
│ id  │ email         │  │ id  │ title   │ user_id │
├─────┼───────────────┤  ├─────┼─────────┼─────────┤
│  1  │ alice@ex.com  │──│  1  │ Post A  │    1    │
│     │               │  │  2  │ Post B  │    1    │
│  2  │ bob@ex.com    │──│  3  │ Post C  │    2    │
└─────┴───────────────┘  └─────┴─────────┴─────────┘

One-to-One

Each user has exactly one profile.

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL
);

CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  display_name text,
  avatar_url text,
  bio text
);

The profile's id is both a primary key and a foreign key to users.

Why separate tables?

• Keep frequently accessed data separate from rarely accessed data
• Different access permissions (profiles public, users private)
• Cleaner schema organization

Many-to-Many

Users can belong to many teams; teams have many users.

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL
);

CREATE TABLE teams (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL
);

-- Junction/join table
CREATE TABLE team_members (
  team_id uuid REFERENCES teams(id) ON DELETE CASCADE,
  user_id uuid REFERENCES users(id) ON DELETE CASCADE,
  role text DEFAULT 'member',
  joined_at timestamptz DEFAULT now(),
  PRIMARY KEY (team_id, user_id)  -- Composite primary key
);

users          team_members           teams
┌────┬────┐    ┌─────────┬─────────┐  ┌────┬──────────┐
│ id │... │    │ team_id │ user_id │  │ id │ name     │
├────┼────┤    ├─────────┼─────────┤  ├────┼──────────┤
│ 1  │    │←───│    1    │    1    │──→│ 1  │ Alpha    │
│    │    │    │    1    │    2    │  │    │          │
│ 2  │    │←───│    2    │    1    │──→│ 2  │ Beta     │
└────┴────┘    └─────────┴─────────┘  └────┴──────────┘

---

Foreign Key Constraints

Basic Syntax

-- Inline definition
user_id uuid REFERENCES users(id)

-- Named constraint
user_id uuid,
CONSTRAINT fk_post_user FOREIGN KEY (user_id) REFERENCES users(id)

-- Multi-column foreign key
FOREIGN KEY (tenant_id, user_id) REFERENCES tenant_users(tenant_id, user_id)

Referential Actions

What happens when the referenced row is deleted or updated?

-- ON DELETE options
REFERENCES users(id) ON DELETE CASCADE    -- Delete related rows
REFERENCES users(id) ON DELETE SET NULL   -- Set to NULL
REFERENCES users(id) ON DELETE SET DEFAULT -- Set to default
REFERENCES users(id) ON DELETE RESTRICT   -- Prevent deletion
REFERENCES users(id) ON DELETE NO ACTION  -- Error (default)

-- ON UPDATE options (same choices)
REFERENCES users(id) ON UPDATE CASCADE    -- Update foreign keys too

Choosing the Right Action

Relationship	Recommended Action	Why
User → Posts	CASCADE	Delete user's posts when user deleted
User → Comments	CASCADE or SET NULL	Depends on requirements
Order → Product	RESTRICT	Can't delete product with orders
Post → Author	SET NULL	Keep posts, show "deleted user"

Example decisions:

-- Posts deleted with user
user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE

-- Comments preserved, author becomes null
author_id uuid REFERENCES auth.users(id) ON DELETE SET NULL

-- Can't delete category with products
category_id uuid REFERENCES categories(id) ON DELETE RESTRICT

---

Querying Relationships

Inner Join (Most Common)

Returns only matching rows:

SELECT
  posts.title,
  users.email as author_email
FROM posts
INNER JOIN users ON posts.user_id = users.id;

Left Join

Returns all rows from left table, matching from right:

-- All users, even those without posts
SELECT
  users.email,
  COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id;

Supabase Client Queries

The Supabase client makes joins elegant:

// One-to-many: Post with author
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    author:users(
      id,
      email,
      name
    )
  `)

// Result
{
  id: '...',
  title: 'My Post',
  author: {
    id: '...',
    email: 'alice@example.com',
    name: 'Alice'
  }
}

// Many-to-many: User with teams
const { data } = await supabase
  .from('users')
  .select(`
    id,
    email,
    teams:team_members(
      role,
      team:teams(
        id,
        name
      )
    )
  `)
  .eq('id', userId)

---

Self-Referencing Relationships

Tables that reference themselves:

Hierarchical Data (Tree Structure)

-- Categories with subcategories
CREATE TABLE categories (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  parent_id uuid REFERENCES categories(id) ON DELETE CASCADE
);

-- Insert hierarchy
INSERT INTO categories (id, name, parent_id) VALUES
  ('root', 'Electronics', NULL),
  ('phones', 'Phones', 'root'),
  ('iphones', 'iPhones', 'phones');

Recursive Query

-- Get all descendants
WITH RECURSIVE category_tree AS (
  -- Base case: start with root
  SELECT id, name, parent_id, 1 as depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive case: add children
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

---

Common Patterns in Supabase

User Profile Pattern

-- Extend auth.users with profile data
CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username text UNIQUE,
  full_name text,
  avatar_url text,
  website text,
  updated_at timestamptz DEFAULT now()
);

-- Trigger to create profile on signup
CREATE FUNCTION create_profile_for_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id)
  VALUES (NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION create_profile_for_user();

Polymorphic Relationships

When one table can reference multiple other tables:

-- Comments on posts OR comments
CREATE TABLE comments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  content text NOT NULL,
  user_id uuid REFERENCES auth.users(id),

  -- Polymorphic reference
  commentable_type text NOT NULL CHECK (commentable_type IN ('post', 'comment')),
  commentable_id uuid NOT NULL
);

-- Index for querying
CREATE INDEX idx_comments_commentable
ON comments(commentable_type, commentable_id);

Alternative: Separate foreign keys (simpler queries, more nullable columns)

CREATE TABLE comments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  content text NOT NULL,
  post_id uuid REFERENCES posts(id) ON DELETE CASCADE,
  parent_comment_id uuid REFERENCES comments(id) ON DELETE CASCADE,
  CHECK (
    (post_id IS NOT NULL AND parent_comment_id IS NULL) OR
    (post_id IS NULL AND parent_comment_id IS NOT NULL)
  )
);

---

Design Best Practices

1. Always Use Foreign Keys

Don't rely on application code to maintain referential integrity:

-- Wrong: No constraint
user_id uuid  -- Orphaned references possible

-- Right: Constraint enforced
user_id uuid REFERENCES users(id)

2. Choose Appropriate Actions

Think through what should happen when data is deleted:

-- Will deleting a user cascade to comments? Is that intended?
user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE

3. Index Foreign Keys

PostgreSQL doesn't automatically index foreign keys:

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

4. Name Constraints Clearly

Makes debugging easier:

CONSTRAINT fk_posts_user
  FOREIGN KEY (user_id)
  REFERENCES users(id)
  ON DELETE CASCADE

---

Key Takeaways

1. Relationships reduce redundancy: Store data once, reference it
2. Foreign keys enforce integrity: Invalid references impossible
3. Referential actions matter: Think about delete/update behavior
4. Index foreign keys: PostgreSQL doesn't do this automatically
5. Supabase client handles joins: Clean syntax for nested data
6. Self-references work: Hierarchies and trees are possible

---

Next Steps

With relationships modeled, we'll explore how indexes improve query performance—essential knowledge as your data grows.

---

A well-designed schema is like a good contract: it clearly defines what's valid and prevents violations before they happen.