PostgreSQL Foreign Key Cycles in Multi-Tenant Hierarchies: Breaking Circular Dependencies Without Violating Referential Integrity

I hit this problem hard in CitizenApp. We needed users to belong to teams, teams to have manager users, and managers to delegate permissions back through teams. Sounds reasonable, right? PostgreSQL said no.

The naive schema looks clean on paper:

CREATE TABLE teams (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  name VARCHAR(255) NOT NULL,
  manager_id UUID NOT NULL REFERENCES users(id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

CREATE TABLE users (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  email VARCHAR(255) NOT NULL,
  team_id UUID NOT NULL REFERENCES teams(id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

Try inserting data and watch it explode:

ERROR: insert or update on table "teams" violates foreign key constraint
FOREIGN KEY (manager_id) REFERENCES users(id)

You can't insert a user without a team. You can't insert a team without a user manager. Welcome to circular hell.

Why This Matters (Beyond the Error)

I've seen three bad production approaches to this:

1. Soft deletes everywhere — "We'll just mark things as deleted instead of cascading." Now your queries are littered with AND deleted_at IS NULL. Your indexes are bloated. Your team hates you.

2. Nullable foreign keys — Set manager_id NULL initially. But now you've violated your actual business constraint (every team must have a manager). Your application becomes the database.

3. No foreign keys — "We'll just trust the app." I've watched this drift into data chaos within six months.

There's a fourth way: DEFERRABLE constraints with transaction-level validation. This is what I use in CitizenApp, and it's been solid.

The Solution: DEFERRABLE INITIALLY DEFERRED

PostgreSQL lets you declare foreign keys as DEFERRABLE INITIALLY DEFERRED. This means:

• Foreign key checks happen at transaction commit, not at statement execution
• Within a transaction, you can temporarily violate constraints
• As long as everything is valid at COMMIT, you're golden
• Referential integrity is still guaranteed—nothing weak about it

Here's the fixed schema:

CREATE TABLE teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  name VARCHAR(255) NOT NULL,
  manager_id UUID NOT NULL REFERENCES users(id) 
    ON DELETE RESTRICT
    DEFERRABLE INITIALLY DEFERRED,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  UNIQUE(tenant_id, name)
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  email VARCHAR(255) NOT NULL,
  team_id UUID NOT NULL REFERENCES teams(id)
    ON DELETE RESTRICT
    DEFERRABLE INITIALLY DEFERRED,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  UNIQUE(tenant_id, email)
);

Notice: both foreign keys are DEFERRABLE INITIALLY DEFERRED. The ON DELETE RESTRICT is important—we never want orphaned records cascading silently.

Insertion Strategy: Create in the Right Order

Within a single transaction, order matters:

# FastAPI + SQLAlchemy example
from sqlalchemy import text
from sqlalchemy.orm import Session
import uuid

async def create_team_with_manager(
    db: Session,
    tenant_id: str,
    team_name: str,
    manager_email: str,
) -> dict:
    """
    Creates a team and its manager in a single transaction.
    The cycle (team → manager user → team) is resolved at commit.
    """
    team_id = str(uuid.uuid4())
    user_id = str(uuid.uuid4())

    try:
        # Step 1: Create the user WITHOUT team assignment
        # Temporarily violate the team_id constraint
        db.execute(
            text("""
            INSERT INTO users (id, tenant_id, email, team_id)
            VALUES (:id, :tenant_id, :email, :team_id)
            """),
            {
                "id": user_id,
                "tenant_id": tenant_id,
                "email": manager_email,
                "team_id": team_id,  # Will reference team created next
            }
        )

        # Step 2: Create the team pointing to the user
        db.execute(
            text("""
            INSERT INTO teams (id, tenant_id, name, manager_id)
            VALUES (:id, :tenant_id, :name, :manager_id)
            """),
            {
                "id": team_id,
                "tenant_id": tenant_id,
                "name": team_name,
                "manager_id": user_id,
            }
        )

        # Step 3: Update the user's team_id to the correct team
        db.execute(
            text("""
            UPDATE users SET team_id = :team_id WHERE id = :id
            """),
            {"team_id": team_id, "id": user_id}
        )

        db.commit()

        return {
            "team_id": team_id,
            "user_id": user_id,
            "team_name": team_name,
            "manager_email": manager_email,
        }

    except Exception as e:
        db.rollback()
        raise ValueError(f"Failed to create team: {str(e)}")

Wait—that's three steps. Can we do better? Absolutely. Use a prepared transaction or a function:

CREATE FUNCTION create_team_with_manager(
  p_tenant_id UUID,
  p_team_name VARCHAR,
  p_manager_email VARCHAR
) RETURNS TABLE (team_id UUID, user_id UUID) AS $$
DECLARE
  v_team_id UUID := gen_random_uuid();
  v_user_id UUID := gen_random_uuid();
BEGIN
  -- Insert user first, pointing to team that doesn't exist yet
  INSERT INTO users (id, tenant_id, email, team_id)
  VALUES (v_user_id, p_tenant_id, p_manager_email, v_team_id);

  -- Now insert team pointing to user
  INSERT INTO teams (id, tenant_id, name, manager_id)
  VALUES (v_team_id, p_tenant_id, p_team_name, v_user_id);

  RETURN QUERY SELECT v_team_id, v_user_id;
END;
$$ LANGUAGE plpgsql;

The function wraps everything in one logical transaction. Call it from Python:

async def create_team_with_manager_simple(
    db: Session,
    tenant_id: str,
    team_name: str,
    manager_email: str,
) -> dict:
    result = db.execute(
        text("""
        SELECT team_id, user_id 
        FROM create_team_with_manager(:tenant_id, :team_name, :manager_email)
        """),
        {
            "tenant_id": tenant_id,
            "team_name": team_name,
            "manager_email": manager_email,
        }
    ).fetchone()

    db.commit()
    return {"team_id": str(result[0]), "user_id": str(result[1])}

Deleting: Watch the Cascade

This is where ON DELETE RESTRICT saves you:

-- This will FAIL at commit if the user is still a manager
DELETE FROM users WHERE id = ?;
-- PostgreSQL: Cannot delete user while still managing a team

You must delete the team first, or reassign the manager:

async def delete_team(db: Session, team_id: str):
    # Reassign manager to a different team, or NULL (if allowed)
    db.execute(
        text("UPDATE teams SET manager_id = NULL WHERE id = :id"),
        {"id": team_id}
    )

    # Now safe to delete
    db.execute(
        text("DELETE FROM teams WHERE id = :id"),
        {"id": team_id}
    )

    db.commit()

This is exactly what you want—explicit, intentional, no silent cascades.

Gotcha: DEFERRABLE Only Works Within Transactions

This burned me: I tested locally (wrapped in transactions), deployed to production, and found that single-statement inserts outside transactions still fail:

# This FAILS even with DEFERRABLE INITIALLY DEFERRED
user = User(id=user_id, team_id=team_id)
db.add(user)
db.flush()  # Checked immediately, not deferred!

The trick: Always wrap multi-step operations in explicit transactions:

from contextlib import contextmanager

@contextmanager
def atomic_transaction(db: Session):
    try:
        yield db
        db.commit()
    except Exception:
        db.rollback()
        raise

# Usage
with atomic_transaction(db) as session:
    create_team_with_manager(session, ...)

Why Not Soft Deletes or NULLs?

• Soft deletes pollute every query and hide actual data shape
• Nullable FKs mean your database doesn't enforce your actual constraints
• DEFERRABLE enforces referential integrity and allows cycles without workarounds

It's the cleaner architectural choice. I've used this pattern across CitizenApp's tenant hierarchies (8 months, no integrity issues, no maintenance headaches).

Use it. Your future self will thank you.