A join table that cannot contain duplicate memberships

A project can have many accounts, and one account can join many projects. The membership is the relationship between them. It needs an identity of its own, even when that identity is simply the two endpoints taken together.

If you know basic SQL and are adding project access to an application, this article is for you. We will build a tenant-scoped join table, preserve an existing role during a repeated add, and investigate a query that returns no row during a concurrency race even though the membership exists. Run the examples in a disposable PostgreSQL database. They describe storage behavior; they do not authenticate an invitation recipient or authorize a caller to grant access.

Put the tenant inside both references

Suppose project and account numbers are local to a tenant. Project 10 in tenant 1 and project 10 in tenant 2 are different projects. A membership must point to a project and account in the same tenant. Giving the membership a tenant column while referencing only the other numbers would not express that rule.

CREATE TABLE tenants (id bigint PRIMARY KEY);
CREATE TABLE projects (
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  id bigint NOT NULL,
  title text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);
CREATE TABLE accounts (
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  id bigint NOT NULL,
  display_name text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);
CREATE TABLE memberships (
  tenant_id bigint NOT NULL,
  project_id bigint NOT NULL,
  account_id bigint NOT NULL,
  role text NOT NULL CHECK (role IN ('viewer', 'editor')),
  joined_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (tenant_id, project_id, account_id),
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects(tenant_id, id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, account_id)
    REFERENCES accounts(tenant_id, id) ON DELETE CASCADE
);

The membership primary key is (tenant_id, project_id, account_id). Changing role cannot make a second row for the same relationship acceptable. An account can still join a different project, and a project can admit another account. The key forbids duplicate pairs within one tenant, not either legitimate direction of this many-to-many relationship.

Each composite foreign key reuses the membership’s single tenant_id. That makes the tenant part of both endpoint checks. All three identity columns are non-null, so an incomplete reference cannot slip through the ordinary nullable foreign-key behavior. PostgreSQL’s constraint documentation describes overlapping primary and foreign keys for join tables.

Seed two tenants, including deliberately repeated local identifiers:

INSERT INTO tenants VALUES (1), (2);
INSERT INTO projects VALUES
  (1, 10, 'Camera'), (1, 11, 'Sound'), (2, 10, 'Lighting');
INSERT INTO accounts VALUES
  (1, 73, 'Alex'), (1, 74, 'Sam'), (2, 73, 'Jo'), (2, 75, 'Lee');
INSERT INTO memberships (tenant_id, project_id, account_id, role)
VALUES (1, 10, 73, 'editor'), (1, 10, 74, 'viewer'),
       (2, 10, 73, 'viewer');
SELECT tenant_id, project_id, account_id, role
FROM memberships ORDER BY tenant_id, project_id, account_id;

The result contains three memberships. Tenant 1’s account 73 is an editor on its project 10; tenant 2’s account 73 is a viewer on its own project 10. Those are distinct identities, not conflicting roles on one membership.

Now consider (1, 10, 75). Account 75 exists, but only in tenant 2. Inserting that membership fails with foreign-key violation 23503. Likewise, (2, 11, 73) fails because project 11 exists only in tenant 1. Checking each bare number somewhere in the database would have accepted both bad combinations.

View full-size image ↗

This is a deliberate model for tenant-owned accounts. If your product has global accounts that can join several organizations, model that identity separately and define which tenant-account relationship a membership references. Do not attach a tenant column to a global identity merely to copy this schema. In either model, referential integrity still does not decide which authenticated caller may read or change the rows.

An add request should not quietly replace a role

Repeat the attempt to add account 73 as a viewer:

INSERT INTO memberships (tenant_id, project_id, account_id, role)
VALUES (1, 10, 73, 'viewer')
ON CONFLICT ON CONSTRAINT memberships_pkey DO NOTHING
RETURNING role;

SELECT role FROM memberships
WHERE tenant_id = 1 AND project_id = 10 AND account_id = 73;

The insertion returns no row. The following query returns editor. DO NOTHING preserves the stored role and join time; it does not downgrade the account because the repeated request supplied a weaker role. Naming the primary-key constraint identifies the conflict this example handles. Inferring the same columns with ON CONFLICT (tenant_id, project_id, account_id) is another supported form and can be more convenient when replacing indexes.

The INSERT reference states that RETURNING includes rows actually inserted or updated. A skipped row is not a returned confirmation of the existing relationship. The application must decide whether “already an editor” satisfies “add as viewer,” should be reported as an existing membership, or conflicts with a stricter operation. A database no-op cannot choose that response.

DO UPDATE SET role = EXCLUDED.role has a different meaning: replace the current role with the incoming role. The fixture’s comparison confirms that it changes this editor to viewer. Use a separately authorized role-change operation when replacement is intended. Avoid adding an update solely to force the database to return a row; an update can invoke other behavior that a duplicate add should not request.

The conflict action is not blanket validation forgiveness. An incoming owner role still fails this schema’s CHECK, including when the pair already exists. Null keys and references to missing endpoints remain errors. Handle their actual meaning instead of converting every failed write into “already a member.”

Keep a role transition on the existing relationship

An explicit transition can require the role the caller expects:

BEGIN;
UPDATE memberships SET role = 'editor'
WHERE tenant_id = 1 AND project_id = 10 AND account_id = 74
  AND role = 'viewer'
RETURNING role;
SELECT account_id, role FROM memberships
WHERE tenant_id = 1 AND project_id = 10 ORDER BY account_id;
ROLLBACK;

Inside this transaction, account 74 changes from viewer to editor while account 73 remains editor. The final rollback restores the sample viewer. The predicate prevents this command from changing a row whose current role is already different. A zero-row result requires interpretation: the membership may be absent, already changed, or outside the supplied scope. It is not proof that the caller had permission.

The expected-role condition is also not a complete history check. A role could have changed away and back between observations. If the operation must reject every intervening edit, introduce a version or another explicit concurrency contract. The simple table records one current role and a join timestamp; it does not maintain role history or establish a last-owner rule. Adding owner to the allowed strings would not enforce “every project keeps an owner.”

Delete the relationship or the endpoint deliberately

Removing a person from a project should target the membership, leaving both endpoints available. Deleting a project has a wider effect under the chosen cascades:

BEGIN;
DELETE FROM memberships
WHERE tenant_id = 1 AND project_id = 10 AND account_id = 73;
SELECT count(*) FROM projects WHERE tenant_id = 1 AND id = 10;
SELECT count(*) FROM accounts WHERE tenant_id = 1 AND id = 73;
DELETE FROM projects WHERE tenant_id = 1 AND id = 10;
SELECT tenant_id, project_id, account_id FROM memberships
ORDER BY tenant_id, project_id, account_id;
ROLLBACK;

Both endpoint counts after the first delete are one. The later project deletion removes its remaining membership, leaving only tenant 2’s (2, 10, 73) relationship in this transaction. The final rollback restores the starting data. Deleting tenant 1’s account 73 similarly removes that account’s memberships across its projects, without deleting project rows or tenant 2’s account with the same local number.

Cascade direction matters: deleting the parent removes referencing relationships; deleting a relationship does not delete its parents. Choose a different deletion policy if membership history must remain available. A current membership table can be paired with separate history records rather than making one primary key represent every past period of access.

A repeated scoped delete may affect zero rows. The DELETE reference treats that as a successful statement with nothing deleted, not a database error. Whether the API describes the operation as already complete or as a missing relationship is another explicit contract.

A conflict can be visible to insertion but absent from its snapshot

The earlier insert and lookup are separate SQL statements. It is tempting to combine them into one “insert or fetch” expression. This next query is an experiment exposing a limit, not a promise that it always returns a membership:

WITH inserted AS (
  INSERT INTO memberships (tenant_id, project_id, account_id, role)
  VALUES (1, 11, 74, 'viewer')
  ON CONFLICT ON CONSTRAINT memberships_pkey DO NOTHING
  RETURNING role
)
SELECT 'inserted' AS source, role FROM inserted
UNION ALL
SELECT 'existing', role FROM memberships
WHERE tenant_id = 1 AND project_id = 11 AND account_id = 74;

With no competitor, its first execution returns inserted | viewer; a later execution returns existing | viewer. That ordinary sequence misses a race. Start with (1, 11, 74) absent. Connection A begins a transaction and inserts it as editor without committing. Connection B starts this exact query and waits on the conflicting write. Now commit A.

B skips insertion, preserving the editor. Its inserted result is empty. Its other SELECT also returns nothing: the statement’s snapshot was taken before A committed. The conflict check needed to honor A’s completed insertion even though that row was outside the snapshot used for the lookup. One membership exists, but this statement returned zero rows.

This outcome is documented in PostgreSQL’s Read Committed rules. The data-modifying WITH documentation also explains the shared snapshot and why RETURNING communicates changes between the modifying substatement and its consumer. Combining the operations does not refresh the SELECT’s view after a wait.

View full-size image ↗

In the local PostgreSQL 17.11 fixture, a fresh SELECT after B’s statement returned editor. In a separate run where A rolled back, B inserted the viewer and returned inserted | viewer. The test observed B waiting before releasing A and checked the final stored role in both cases. It did not infer the race from two rapidly issued requests.

At Read Committed, a following statement can get a newer snapshot, which is why the earlier two-statement pattern is easier to interpret. That lookup is still not a permanent guarantee: another transaction may delete the membership before it runs or change it afterward. An application needing stronger stability must define locks, retries or another transaction protocol around the operation. These examples do not promise the same recovery behavior at Repeatable Read or Serializable.

Keep invitation consumption outside the pair’s identity

An invitation may exist before an account does, and can expire or be revoked. An active membership identifies a known account and project. These are different records with different states. Accepting an invitation should coordinate its consumption with creation or confirmation of the membership, while checking the recipient’s identity and the allowed role.

This article has not implemented that acceptance transaction. The pair key prevents duplicate memberships; it cannot prove an invitation was valid or consumed exactly once. An email address alone is not an authenticated account identity, and a retry that preserves a row does not settle the invitation’s lifecycle.

For practice, run the duplicate insert with each allowed role and check the stored role and join time. Try the two invalid tenant combinations, remove only the relationship, and then test each endpoint cascade inside a rolled-back transaction. Finally reproduce both outcomes of the waiting query. The useful result is a documented storage invariant and a clearly bounded operation, not merely a successful SQL command.