How to Add a Foreign Key in Supabase

1create table public.categories (
2  id bigint generated always as identity primary key,
3  name text not null,
4  created_at timestamptz default now()
5);

1-- Option 1: Add foreign key when creating a new table
2create table public.posts (
3  id bigint generated always as identity primary key,
4  title text not null,
5  body text,
6  category_id bigint references public.categories(id),
7  user_id uuid references auth.users(id) on delete cascade,
8  created_at timestamptz default now()
9);
10
11-- Option 2: Add foreign key to an existing table
12alter table public.posts
13  add column category_id bigint references public.categories(id);

1-- CASCADE: delete child rows when parent is deleted
2alter table public.posts
3  add constraint fk_posts_category
4  foreign key (category_id)
5  references public.categories(id)
6  on delete cascade;
7
8-- SET NULL: set to null when parent is deleted
9alter table public.posts
10  add constraint fk_posts_category
11  foreign key (category_id)
12  references public.categories(id)
13  on delete set null;
14
15-- RESTRICT (default): prevent parent deletion if children exist
16alter table public.posts
17  add constraint fk_posts_category
18  foreign key (category_id)
19  references public.categories(id)
20  on delete restrict;

1-- Canonical user profiles pattern
2create table public.profiles (
3  id uuid not null references auth.users(id) on delete cascade,
4  display_name text,
5  avatar_url text,
6  primary key (id)
7);
8
9-- Enable RLS
10alter table public.profiles enable row level security;
11
12-- RLS policy: users can read and update their own profile
13create policy "Users manage own profile"
14  on public.profiles for all
15  to authenticated
16  using ((select auth.uid()) = id);

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6)
7
8// Fetch posts with their category name (many-to-one)
9const { data: posts } = await supabase
10  .from('posts')
11  .select(`
12    id,
13    title,
14    categories ( name )
15  `)
16
17// Fetch a category with all its posts (one-to-many)
18const { data: category } = await supabase
19  .from('categories')
20  .select(`
21    id,
22    name,
23    posts ( id, title )
24  `)
25  .eq('id', 1)
26  .single()