A SaaS workspace imports a batch of tasks. One row carries workspace 10 and project 202. Both IDs exist, so the database accepts the row. But project 202 belongs to workspace 20. The import has created a task whose recorded owner and parent disagree.
Multi-tenant foreign keys can prevent that mismatch. In a shared PostgreSQL schema, a composite foreign key can require the task’s workspace ID and project ID to match the same project row. Here’s how to model the relationship, test it with two workspaces, and roll it out on an existing table.
Put the workspace ID in the relationship
Suppose each customer account is a workspace. Each workspace owns projects, and each project has tasks. The application stores workspace_id on tasks so it can list a workspace’s tasks directly, even when there’s no project filter. That makes the ownership rule explicit. Every task must belong to a project in its own workspace.
Two separate foreign keys, one for the workspace and one for the project, prove that both records exist. Each lookup can succeed against a different workspace. A foreign key on (workspace_id, project_id) checks both values against a single project row, so a bad import or background job gets the same check as an API request.
| Task workspace | Task project | Separate foreign keys | Composite foreign key |
|---|---|---|---|
| 10 | 101 | Accepts | Accepts |
| 10 | 202 | Accepts | Rejects |
| 20 | 202 | Accepts | Accepts |
Define the composite foreign key in PostgreSQL
This example keeps each table’s existing style of single-column primary key. On projects, add a unique constraint on (workspace_id, id) so PostgreSQL has a unique pair for the task to reference. The primary key already makes each project ID globally unique, and the additional constraint gives the composite reference the matching key it requires. See the PostgreSQL foreign key documentation for the eligible target keys.
CREATE TABLE workspaces (
id bigint PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE projects (
id bigint PRIMARY KEY,
workspace_id bigint NOT NULL REFERENCES workspaces (id),
name text NOT NULL,
UNIQUE (workspace_id, id)
);
CREATE TABLE tasks (
id bigint PRIMARY KEY,
workspace_id bigint NOT NULL,
project_id bigint NOT NULL,
title text NOT NULL,
CONSTRAINT tasks_project_workspace_fk
FOREIGN KEY (workspace_id, project_id)
REFERENCES projects (workspace_id, id)
);
CREATE INDEX tasks_workspace_project_idx
ON tasks (workspace_id, project_id);Both values on the task are required. With PostgreSQL’s default foreign key matching, a null in either column would let the row skip the pair check. The project’s own foreign key already guarantees that its workspace exists, so this model can establish task ownership through the project. The index on the task columns supports lookups by workspace and project and helps PostgreSQL find referencing rows when a project is deleted or its key changes. PostgreSQL creates the supporting index for the parent’s unique constraint automatically. Add the child index explicitly if you need it.
The schema uses fixed IDs to keep the example easy to run. In an application, use your existing identity or UUID strategy. Keep the two sides of the relationship in the same column order and use matching types.
Test with two workspaces
Run the setup below, then run the final insert on its own. The setup creates a valid task in workspace 10. The final insert fails with SQLSTATE 23503 because the pair (10, 202) is missing from projects, even though project 202 exists.
INSERT INTO workspaces (id, name)
VALUES (10, 'North Studio'), (20, 'South Studio');
INSERT INTO projects (id, workspace_id, name)
VALUES (101, 10, 'Website'), (202, 20, 'Mobile app');
INSERT INTO tasks (id, workspace_id, project_id, title)
VALUES (1, 10, 101, 'Check the homepage');-- Expected result is a foreign key violation.
INSERT INTO tasks (id, workspace_id, project_id, title)
VALUES (2, 10, 202, 'Check the onboarding screen');Put the same case in your integration tests. Check that a task can move to a second project in its workspace and that a move to another workspace’s project fails. The example leaves the default foreign key actions in place, so a project with dependent tasks cannot simply be deleted or moved to a different workspace while its tasks still refer to the old pair. Make intended transfers an explicit workflow with permission checks and a plan for dependent records.
Add the constraint to existing tables
If tasks already store both IDs, start by finding rows whose pair doesn’t resolve. A missing pair can mean that ownership, the project reference, or both are wrong. Check the original import or audit records before correcting it. Automatically copying the project’s workspace onto the task could assign data to the wrong customer.
SELECT t.id, t.workspace_id, t.project_id
FROM tasks AS t
LEFT JOIN projects AS p
ON p.workspace_id = t.workspace_id
AND p.id = t.project_id
WHERE p.id IS NULL;For a large table, plan the rollout in stages. Make sure application writes supply both values. On the parent, create a non-partial unique index on the pair if a suitable key is missing. On the child, plan an index for the new lookup. PostgreSQL offers concurrent index builds to allow writes during the build. Run each concurrent build outside a transaction block and account for the extra work it does.
On an ordinary unpartitioned table, you can add the foreign key with NOT VALID to enforce it for new relationships while deferring the existing-row scan. Repair any old mismatches and run VALIDATE CONSTRAINT. If either column still allows nulls, backfill and enforce the required values as part of the rollout. Try the migration against a representative copy and plan for the locks involved. See the PostgreSQL ALTER TABLE documentation.
Keep access checks at the workspace boundary
The foreign key enforces agreement between the task and its project. A write with a valid pair from workspace 20 can still succeed if the database role can write it. Scope reads and writes to the authorized workspace in your application. If you use PostgreSQL row-level security, test the policy with the database role your application actually uses. Table owners normally bypass row-level security.
The same relationship rule applies when a webhook delivery connects an event and an endpoint that both belong to a customer. Our webhook database design guide shows where those relationships sit in a delivery schema.
Sketch the workspace, project, and task tables in DB Designer. Label the project relationship with both columns, then walk through a valid task and a mismatched task with your team. The model is ready for implementation when everyone can point to the exact key that should reject the bad row.

Recent Comments