Relationships
Most apps have tables that connect to each other: an order belongs to a user, a product has many reviews, a project has many members. These connections are called foreign keys — a column in one table that references a row in another.
Creating a foreign key
There's no visual "Reference" column type — foreign keys are defined in SQL (in the SQL editor) or by asking your AI, which applies them as an MCP migration.
The pattern is a regular column plus a REFERENCES clause:
-- Add the FK as part of CREATE TABLE
CREATE TABLE public.orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id TEXT NOT NULL REFERENCES system.users(id) ON DELETE CASCADE,
total NUMERIC(10,2) NOT NULL
);
-- ...or add one to an existing table
ALTER TABLE public.orders
ADD CONSTRAINT orders_user_id_fkey
FOREIGN KEY (user_id) REFERENCES system.users(id) ON DELETE CASCADE;
A few rules of thumb when wiring up a foreign key:
- Name the column after what it points to —
user_id,product_id. - Match the target's primary key type — usually
uuid. Exception: if it references a user, usetext(see Tables & Schema). - Pick an
ON DELETEbehavior — what happens when the referenced row is deleted (see below).
Let your AI do it
"Add a user_id foreign key on orders that cascades on delete." — your AI client writes the ALTER TABLE and applies it as a migration for you.
ON DELETE behavior
When you delete a row that others reference, Postgres needs to know what to do with the referrers:
| Option | What it does | Use for |
|---|---|---|
| CASCADE | Delete the referring rows too | A user's tasks, a post's comments, a session's messages |
| SET NULL | Keep referrers, set the FK to NULL | An author on a post — if the author leaves, keep the post |
| RESTRICT | Block the delete entirely | Products referenced by orders — never delete a product if it's in someone's order history |
| NO ACTION | Same as RESTRICT in most cases | Rarely used |
The safest default for user-owned data is CASCADE. When the user is deleted, their tasks go with them.
One-to-many
The common case: one user has many tasks, one post has many comments. Put the foreign key on the many side:
CREATE TABLE public.posts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL,
user_id TEXT NOT NULL REFERENCES system.users(id) ON DELETE CASCADE
);
CREATE TABLE public.comments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
post_id UUID NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
content TEXT NOT NULL
);
One post has many comments. The post_id column lives on comments.
Many-to-many
When both sides can have many of the other — a user can be in many projects, a project has many users — use a pivot table (also called a join table):
CREATE TABLE public.project_members (
project_id UUID NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES system.users(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member',
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (project_id, user_id)
);
The primary key is the combination of both columns — a user can only be in a project once. Add extra columns (role, joined_at) on the pivot table itself when you need metadata about the relationship.
Querying across relationships
To get data from related tables in one request, use a SQL JOIN in your workflow's database node:
SELECT
posts.id,
posts.title,
COUNT(comments.id) AS comment_count
FROM public.posts
LEFT JOIN public.comments ON comments.post_id = posts.id
WHERE posts.user_id = ${current_user_id}
GROUP BY posts.id
ORDER BY posts.created_at DESC;
See API Builder for how to use custom SQL inside a workflow.