# How to Add a Foreign Key in Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 10-15 min
- Compatibility: Supabase (all plans), PostgreSQL 15+
- Last updated: March 2026

## TL;DR

To add a foreign key in Supabase, use ALTER TABLE with a REFERENCES clause in the SQL Editor or use the Table Editor's column settings to link one table to another. Foreign keys enforce referential integrity, ensuring that a value in one column always matches a valid row in the referenced table. Choose CASCADE or RESTRICT for delete behavior depending on whether child rows should be removed or preserved when the parent is deleted.

## Adding Foreign Key Constraints in Supabase

Foreign keys are the foundation of relational data modeling in Supabase. They create links between tables, ensure data integrity, and enable the Supabase JS client to automatically join related data in queries. This tutorial covers creating foreign keys via SQL and the Dashboard Table Editor, linking to auth.users, and choosing the right delete behavior for your use case.

## Before you start

- A Supabase project with at least one existing table
- Access to the SQL Editor or Table Editor in the Dashboard
- Basic understanding of SQL and relational databases
- Familiarity with the Supabase JavaScript client

## Step-by-step guide

### 1. Create a parent table with a primary key

A foreign key references the primary key (or unique column) of another table. Start by creating the parent table that will be referenced. In this example, we create a categories table with an auto-generated bigint primary key. Every category has a unique ID that other tables can reference.

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

**Expected result:** The categories table is created with an auto-incrementing id column as the primary key.

### 2. Add a foreign key column to the child table using SQL

Add a column to the child table that references the parent table's primary key. The REFERENCES keyword creates the foreign key constraint. You can add it when creating the table or alter an existing table. The column type must match the referenced column type exactly. In this example, we add a category_id column to a posts table that references categories.

```
-- Option 1: Add foreign key when creating a new table
create table public.posts (
  id bigint generated always as identity primary key,
  title text not null,
  body text,
  category_id bigint references public.categories(id),
  user_id uuid references auth.users(id) on delete cascade,
  created_at timestamptz default now()
);

-- Option 2: Add foreign key to an existing table
alter table public.posts
  add column category_id bigint references public.categories(id);
```

**Expected result:** The posts table has a category_id column that is constrained to only accept values that exist in the categories table.

### 3. Choose the correct ON DELETE behavior

When a referenced parent row is deleted, PostgreSQL needs to know what to do with the child rows. CASCADE deletes all child rows automatically. RESTRICT (the default) prevents the parent from being deleted if children exist. SET NULL sets the foreign key column to null. For user-owned data linked to auth.users, CASCADE is the standard choice — when a user is deleted, their data should be removed too.

```
-- CASCADE: delete child rows when parent is deleted
alter table public.posts
  add constraint fk_posts_category
  foreign key (category_id)
  references public.categories(id)
  on delete cascade;

-- SET NULL: set to null when parent is deleted
alter table public.posts
  add constraint fk_posts_category
  foreign key (category_id)
  references public.categories(id)
  on delete set null;

-- RESTRICT (default): prevent parent deletion if children exist
alter table public.posts
  add constraint fk_posts_category
  foreign key (category_id)
  references public.categories(id)
  on delete restrict;
```

**Expected result:** The foreign key constraint is created with the specified delete behavior.

### 4. Link a table to auth.users

The most common foreign key in Supabase connects a table to auth.users so each row belongs to a specific user. Reference auth.users(id) with ON DELETE CASCADE. This is the canonical pattern for profiles tables, user-generated content, and any data that should be deleted when the user account is removed. The id column in auth.users is a UUID, so your foreign key column must also be UUID type.

```
-- Canonical user profiles pattern
create table public.profiles (
  id uuid not null references auth.users(id) on delete cascade,
  display_name text,
  avatar_url text,
  primary key (id)
);

-- Enable RLS
alter table public.profiles enable row level security;

-- RLS policy: users can read and update their own profile
create policy "Users manage own profile"
  on public.profiles for all
  to authenticated
  using ((select auth.uid()) = id);
```

**Expected result:** The profiles table is linked to auth.users with cascade delete, RLS is enabled, and users can manage their own profile.

### 5. Add a foreign key using the Table Editor in Dashboard

If you prefer a visual approach, open your Supabase Dashboard and navigate to the Table Editor. Click on the table you want to modify, then click Edit Column on the column that should be a foreign key (or add a new column). In the column settings, enable the Foreign Key toggle and select the target table and column. Choose the ON DELETE action from the dropdown. Click Save to apply the constraint.

**Expected result:** The foreign key constraint is visible in the column settings and enforced on all new and existing data.

### 6. Query related data across foreign keys

Supabase PostgREST automatically detects foreign key relationships and lets you query nested data using the select syntax. Pass the related table name as a nested select to get parent or child data in a single request. This avoids multiple round trips and is the recommended way to fetch related data in the Supabase JS client.

```
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

// Fetch posts with their category name (many-to-one)
const { data: posts } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    categories ( name )
  `)

// Fetch a category with all its posts (one-to-many)
const { data: category } = await supabase
  .from('categories')
  .select(`
    id,
    name,
    posts ( id, title )
  `)
  .eq('id', 1)
  .single()
```

**Expected result:** The response includes nested related data — posts contain their category object, and categories contain their array of posts.

## Complete code example

File: `foreign-key-setup.sql`

```sql
-- Create parent table
create table public.categories (
  id bigint generated always as identity primary key,
  name text not null,
  created_at timestamptz default now()
);

-- Create child table with foreign keys
create table public.posts (
  id bigint generated always as identity primary key,
  title text not null,
  body text,
  category_id bigint references public.categories(id) on delete set null,
  user_id uuid not null references auth.users(id) on delete cascade,
  created_at timestamptz default now()
);

-- Enable RLS on both tables
alter table public.categories enable row level security;
alter table public.posts enable row level security;

-- Public read for categories
create policy "Anyone can read categories"
  on public.categories for select
  to anon, authenticated
  using (true);

-- Users can read all posts
create policy "Authenticated users can read posts"
  on public.posts for select
  to authenticated
  using (true);

-- Users can only create their own posts
create policy "Users can create own posts"
  on public.posts for insert
  to authenticated
  with check ((select auth.uid()) = user_id);

-- Users can only update their own posts
create policy "Users can update own posts"
  on public.posts for update
  to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

-- Users can only delete their own posts
create policy "Users can delete own posts"
  on public.posts for delete
  to authenticated
  using ((select auth.uid()) = user_id);

-- Add index on foreign key columns for performance
create index idx_posts_category_id on public.posts(category_id);
create index idx_posts_user_id on public.posts(user_id);
```

## Common mistakes

- **Using a different data type for the foreign key column than the referenced primary key (e.g., int4 referencing a bigint)** — undefined Fix: Ensure the foreign key column type exactly matches the referenced column type. Use bigint for bigint references and uuid for uuid references.
- **Forgetting to add ON DELETE CASCADE when linking to auth.users, causing user deletion to fail if they have related data** — undefined Fix: Always use ON DELETE CASCADE for foreign keys referencing auth.users(id). This ensures user data is cleaned up automatically when an account is deleted.
- **Not adding an index on foreign key columns, causing slow joins on large tables** — undefined Fix: PostgreSQL does not automatically index foreign key columns. Add an explicit index: CREATE INDEX idx_table_column ON table(column).
- **Trying to insert a child row with a foreign key value that does not exist in the parent table** — undefined Fix: Insert the parent row first, then the child row. Or use a transaction to insert both atomically. The foreign key constraint rejects invalid references.

## Best practices

- Always add ON DELETE CASCADE for foreign keys referencing auth.users to ensure clean user data removal
- Add an index on every foreign key column for faster joins and lookups
- Use SET NULL for optional relationships where child records should survive parent deletion
- Use the nested select syntax in the Supabase JS client to fetch related data in a single query
- Create foreign keys in SQL migrations for reproducible schema changes across environments
- Name your constraints descriptively: fk_posts_category instead of relying on auto-generated names
- Enable RLS on all tables with foreign key relationships and write policies for each operation
- Use UUID primary keys when you need globally unique IDs across distributed systems

## Frequently asked questions

### Does PostgreSQL automatically create an index on foreign key columns?

No. Unlike primary keys, PostgreSQL does not automatically index foreign key columns. You should create an index manually on each foreign key column to improve join and lookup performance.

### Can I add a foreign key to an existing column that already has data?

Yes, but all existing values in the column must already exist in the referenced table. If any orphaned values exist, the ALTER TABLE command will fail. Clean up invalid data first.

### What is the difference between CASCADE and RESTRICT?

CASCADE automatically deletes or updates child rows when the parent row is deleted or updated. RESTRICT prevents the parent from being deleted if any child rows reference it. RESTRICT is the default behavior.

### Can I reference a column other than the primary key?

Yes, you can reference any column with a UNIQUE constraint. However, referencing the primary key is the most common and recommended approach.

### How do I remove a foreign key constraint?

Use ALTER TABLE table_name DROP CONSTRAINT constraint_name. Find the constraint name by querying information_schema.table_constraints or checking the Table Editor in the Dashboard.

### Can I have multiple foreign keys on the same table?

Yes. A table can have as many foreign keys as needed. For example, a posts table can reference both categories(id) and auth.users(id) simultaneously.

### Can RapidDev help design my Supabase database schema?

Yes. RapidDev can help design your database schema with proper foreign key relationships, RLS policies, and indexing strategies optimized for your application's access patterns.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-add-a-foreign-key-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-add-a-foreign-key-in-supabase