Foreign keys

The DDL forge emits for relations — REFERENCES clauses, onDelete/onUpdate behaviors, deferred checking, and per-dialect quirks (Mongo's app-layer references, SQLite's per-connection PRAGMA, MySQL's InnoDB requirement). This page covers what forge writes to the database when you declare a relation, plus the patterns for self-references, cycles, composite FKs, and online FK changes.

docs/RELATIONS.md is the surface tour of the relation shapes. This document narrows in on the constraint that comes out the other end — what the database stores, who enforces it, and what engine knobs the forge surface doesn't expose. If something here disagrees with the source, the source is right (src/schema/types.ts for the shape, src/adapters/<dialect>/ddl.ts for the emit, src/adapters/mongo/cascade.ts for the app-side walker).

Contents

• Relation surface vs FK emit · Per-dialect FK syntax
• onDelete behaviors · onUpdate
• Deferred constraint checking · SQLite PRAGMA · MySQL InnoDB
• Mongo cascade walker · Soft-delete
• Self-referential · Composite · Cross-schema
• FK indexes · Online FK add · Dropping a FK · Cycles
• Worked examples · Cross-links

---

Relation surface vs FK emit

Forge exposes one relation primitive: rel.one(target, { on, refs, onDelete }) on the owning side. The DDL is a single FK constraint naming exactly that triple, plus the cascade action.

import { f, model, rel } from 'forge-orm';

const Post = model('posts', {
  id:        f.id(),
  author_id: f.objectId(),
  title:     f.string(),
}).relate(() => ({
  author: rel.one('user', { on: 'author_id', refs: 'id', onDelete: 'Cascade' }),
}));

On the five SQL dialects, that produces a FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE constraint named fk_posts_author_id as part of forge push. The inverse side (rel.many) emits nothing — it's virtual. Two rel.ones pointing at each other create a circular dependency; see double-FK bug in RELATIONS.

What the surface does not cover

The surface is deliberately small. It omits onUpdate, MATCH FULL, DEFERRABLE, SET DEFAULT, and composite multi-column FKs. Each missing knob has a workaround below. The common shape — "child row points at parent, kill children when parent dies" — is fully covered.

---

Per-dialect FK syntax

Every SQL dialect speaks the same standard FK syntax. The forge emitters in src/adapters/<dialect>/ddl.ts produce one of two shapes: inline in CREATE TABLE (SQLite, because adding FKs after the fact requires a full table rebuild) or ALTER TABLE ... ADD CONSTRAINT (Postgres, MySQL, DuckDB, MSSQL — these run after every table exists, so cyclic schemas can be created in any order).

Dialect	Emit shape	Constraint name
Postgres	ALTER TABLE "posts" ADD CONSTRAINT "fk_posts_author_id" FOREIGN KEY ("author_id") REFERENCES "users" ("id") ON DELETE CASCADE	fk_posts_author_id
MySQL	ALTER TABLE `posts` ADD CONSTRAINT `fk_posts_author_id` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE	fk_posts_author_id
SQLite	Inline: FOREIGN KEY("author_id") REFERENCES "users" ("id") ON DELETE CASCADE	none (anonymous)
DuckDB	ALTER TABLE "posts" ADD CONSTRAINT "fk_posts_author_id" FOREIGN KEY ("author_id") REFERENCES "users" ("id") ON DELETE CASCADE (accepted; not enforced)	fk_posts_author_id
MSSQL	ALTER TABLE [posts] ADD CONSTRAINT [fk_posts_author_id] FOREIGN KEY ([author_id]) REFERENCES [users] ([id]) ON DELETE CASCADE	fk_posts_author_id
Mongo	No emit. App-side walker handles cascade at delete time.	n/a

MATCH FULL and deferrable

Forge always emits MATCH SIMPLE (the SQL default — any null column skips the FK check) and NOT DEFERRABLE (checks per statement). For single-column FKs MATCH SIMPLE is identical to MATCH FULL; the distinction only matters under composite FKs with partial nulls. If you need either knob, hand-write the constraint via $executeRaw after forge push. See Deferred constraint checking for the patterns that need deferral.

---

onDelete behaviors

The four actions forge exposes, what each emits, and what the database does at delete time.

Action	Postgres / MySQL / MSSQL / DuckDB	SQLite	Mongo (app-side walker)
Cascade	ON DELETE CASCADE — delete child rows	same (PRAGMA must be on)	recursive deleteMany on children
SetNull	ON DELETE SET NULL — null the FK column	same	updateMany with $unset on the FK
Restrict	ON DELETE RESTRICT — block the delete	same	precount; throws if any child found
NoAction	ON DELETE NO ACTION (default)	same	no-op; orphans stay

Cascade

Deleting the parent removes every child whose FK points at it, in the same statement (SQL) or in a recursive walk (Mongo / DuckDB).

const Post = model('posts', { id: f.id(), author_id: f.objectId() })
  .relate(() => ({
    author: rel.one('user', { on: 'author_id', refs: 'id', onDelete: 'Cascade' }),
  }));

await db.user.delete({ where: { id: 'u1' } });
// Every post with author_id = 'u1' is gone.

The cascade is silent. If the parent is a hot target for accidental deletes, pair Cascade with Restrict on cross-tenant references and audit deletes via the event stream.

SetNull

Keeps the orphan row alive with a null FK — audit-style data where the historical record matters but the reference shouldn't pin the parent's lifecycle.

// FK column MUST be nullable.
const Order = model('orders', {
  id: f.id(),
  buyer_id: f.objectId().optional(),
}).relate(() => ({
  buyer: rel.one('user', { on: 'buyer_id', refs: 'id', onDelete: 'SetNull' }),
}));

await db.user.delete({ where: { id: 'u1' } });
// Orders that pointed at u1 now have buyer_id = NULL; the rows survive.

SetNull on a NOT NULL FK is accepted at schema time and fails at runtime — Postgres raises null value … violates not-null constraint. Always pair SetNull with .optional().

Restrict

Blocks the parent delete if any child still references it.

const Membership = model('memberships', {
  id: f.id(), user_id: f.objectId(), role_id: f.objectId(),
}).relate(() => ({
  role: rel.one('role', { on: 'role_id', refs: 'id', onDelete: 'Restrict' }),
}));

await db.role.delete({ where: { id: 'admin' } });
// Throws — at least one membership still references this role.

Use it for roles, plans, currencies, anything referenced from a lot of rows and shouldn't quietly mass-cascade.

NoAction

The default when onDelete is omitted. On SQL it emits ON DELETE NO ACTION — semantically equivalent to Restrict on non-deferrable constraints (forge's default). The distinction only matters under deferrable constraints, where RESTRICT checks at the statement and NO ACTION checks at commit.

On Mongo, NoAction is genuinely "do nothing" — children are left as orphans pointing at a deleted parent.

---

onUpdate and why forge doesn't expose it

The SQL standard supports the same five actions on ON UPDATE as on ON DELETE. Forge does not expose onUpdate — f.id() produces a stable identifier (cuid / objectid / ulid) and the PK never changes in normal operation, so every dialect's default ON UPDATE NO ACTION is the right call.

If you genuinely need ON UPDATE CASCADE — you picked a natural key like email as the refs column and want renames to propagate — add it after forge push via raw DDL:

await db.$executeRaw`
  ALTER TABLE invitations
    DROP CONSTRAINT fk_invitations_to,
    ADD  CONSTRAINT fk_invitations_to
         FOREIGN KEY (to) REFERENCES users(email)
         ON DELETE CASCADE
         ON UPDATE CASCADE
`;

Drop-and-add is necessary because no dialect has an "alter constraint" verb — you replace it wholesale. Drift detection will flag the constraint as "extra" on the next forge push unless the raw DDL lives in a migration script.

SET DEFAULT

The SQL standard also defines ON DELETE SET DEFAULT. Forge doesn't expose it; same workaround. MySQL/InnoDB silently treats SET DEFAULT as NO ACTION anyway, and the behaviour is rarely useful (children now point at a tombstone default rather than the deleted parent).

---

Deferred constraint checking

Forge emits NOT DEFERRABLE constraints — the FK check fires at the end of each statement. That's fine for typical workloads. The case that bites is bulk insert into cyclic relations: two tables with FKs pointing at each other, where you'd like to insert a batch of A then a batch of B without re-ordering by FK direction.

Postgres is the only dialect of the five that supports DEFERRABLE INITIALLY DEFERRED — checks moved to commit time. Opt in via raw DDL:

await db.$executeRaw`
  ALTER TABLE comments
    DROP CONSTRAINT fk_comments_parent_id,
    ADD  CONSTRAINT fk_comments_parent_id
         FOREIGN KEY (parent_id) REFERENCES comments(id)
         ON DELETE CASCADE
         DEFERRABLE INITIALLY DEFERRED
`;

Then bulk-insert inside a transaction:

await db.$transaction(async (tx) => {
  await tx.comment.createMany({ data: messages });   // refs may not exist yet
  // At COMMIT the deferred check resolves — every parent_id has a row.
});

If any row's FK is still unresolved at commit time the whole transaction rolls back.

MySQL has no deferrable constraints. Toggle SET FOREIGN_KEY_CHECKS = 0 for the load via $executeRaw, then = 1 after — accept that the constraint is un-checked during the gap.

SQLite has no deferrable constraints either; toggling PRAGMA foreign_keys = OFF suppresses every FK in the connection. Insert the parent batch first, then the children.

MSSQL supports WITH NOCHECK ADD CONSTRAINT to add the FK without validating existing rows but doesn't defer runtime checks. NOCHECK CONSTRAINT all disables for a load — same caveat as MySQL.

DuckDB accepts FK DDL but doesn't enforce it, so deferral is moot.

---

SQLite quirks — the per-connection PRAGMA

SQLite parses FK constraints as part of CREATE TABLE but only enforces them when PRAGMA foreign_keys = ON is set. The pragma is session-scoped — every freshly-opened connection starts with it OFF.

Forge sets the pragma automatically on every connection it opens — src/adapters/sqlite/adapter.ts:57 during connect, src/adapters/sqlite/migrate.ts:25 during migrations (the pragma is session-scoped and a freshly-opened DB starts off), and src/wasm/worker.ts:95 plus worker-pro.ts:76 for the browser WASM workers.

The browser doctor (src/wasm/browser-doctor.ts:113) probes PRAGMA foreign_keys on startup and reports foreign_keys: 1 when enforcement is active — useful when injecting a custom WASM build.

What happens if the pragma is off

The FK constraint sits in the schema and is reported by PRAGMA foreign_key_list, but violating inserts succeed silently and deletes don't cascade. Drift detection still considers the constraint present, so forge push won't try to recreate it — the shape is correct, the engine just isn't checking. The symptom is "cascades don't fire, no error" — run PRAGMA foreign_keys against the connection to confirm.

Custom drivers

Custom SQLite drivers passed via createDb({ driver }) set the pragma themselves; forge's built-in betterSqlite3Driver wrapper handles it in connect. The fix for an injected driver is one extra line:

const driver = {
  async connect(url) {
    await db.exec('PRAGMA journal_mode = WAL');
    await db.exec('PRAGMA foreign_keys = ON');         // mandatory
  },
  // ...
};

See docs/SQLITE.md for the full driver contract.

---

MySQL InnoDB requirement

MySQL has two storage engines that matter here: InnoDB (the default since 5.5) enforces FKs; MyISAM accepts the FK syntax but ignores the constraint entirely. Forge does not pin the engine in its CREATE TABLE — it inherits whatever default_storage_engine is set to.

Modern MySQL / MariaDB defaults to InnoDB. For legacy tables, check:

SHOW TABLE STATUS WHERE Name = 'posts';   -- Engine column
ALTER TABLE posts ENGINE = InnoDB;        -- convert if needed

Forge has no auto-conversion path. The doctor probe flags MyISAM tables with FK constraints declared.

Other InnoDB gotchas

SET DEFAULT is silently downgraded to NO ACTION. FOREIGN_KEY_CHECKS = 0 is per-session and suppresses every constraint at once — there's no per-constraint toggle. FKs to virtual generated columns are rejected; stored generated columns are fine.

---

Mongo — no native FKs, the cascade walker

Mongo has no FK concept at the storage layer. Forge implements the same onDelete semantics application-side via the walker in src/adapters/mongo/cascade.ts. On every delete / deleteMany, the walker:

1. Inspects the schema for every owning relation pointing at the model being deleted (rel.one with matching target; inverse-side rel.many is skipped).
2. For each owning relation with onDelete: 'Cascade', recursively collects child rows and deletes them.
3. For each onDelete: 'SetNull', runs updateMany with $unset: { <fk>: '' }.
4. Carries a visited set (collection + _id) across the recursion to break cycles.
5. Walks leaves first — recursive call before the local deleteMany, so children are gone before parents.

Restrict runs a countDocuments precount; > 0 throws.

Atomicity caveat

On standalone Mongo (no replica set) the walker is best-effort — if the second cascade level fails after the first succeeded, the partial state is left behind. There's no compensating "uncreate" pass. On a replica set, db.$transaction pins every cascade write to one session and the whole tree commits or aborts together:

await db.$transaction(async (tx) => {
  await tx.user.delete({ where: { id: 'u1' } });
});

Raw delete bypass

db.$runCommandRaw and direct MongoDB driver calls do not run the walker. Raw deleteMany on a parent collection leaves children orphaned.

Reads still use $lookup

Mongo relation reads hydrate via $lookup — the relation surface is identical to SQL. Only writes differ. See docs/MONGO.md for the full adapter surface.

DuckDB falls in the same bucket

DuckDB accepts FK DDL at CREATE TABLE time but doesn't enforce the constraint at runtime — DuckDB is OLAP-shaped and treats relational integrity as documentation (src/adapters/duckdb/adapter.ts:23). The nativeCascades: false capability flag triggers the same app-side walker on DuckDB that runs on Mongo.

---

FK and soft-delete

softDelete() rewires the model so db.x.delete flips a deleted_at column rather than physically removing the row. Because the row stays in the table, the FK constraint never fires — and neither does the cascade walker.

const Order = model('orders', {
  id: f.id(), buyer_id: f.objectId().optional(),
  deleted_at: f.dateTime().optional(),
}).softDelete()
  .relate(() => ({
    buyer: rel.one('user', { on: 'buyer_id', refs: 'id', onDelete: 'Cascade' }),
  }));

await db.order.delete({ where: { id: 'o1' } });       // soft — sets deleted_at
await db.order.deleteHard({ where: { id: 'o1' } });   // hard delete + cascade

Cascading soft-deletes

The cascade walker is hard-delete-only by design. Chain the soft-delete to children manually:

await db.$transaction(async (tx) => {
  await tx.orderItem.updateMany({
    where: { order_id: orderId },
    data: { deleted_at: new Date() },
  });
  await tx.order.delete({ where: { id: orderId } });
});

Restrict on a soft-deleted parent is a no-op — the row never goes away. If you need "can't soft-delete this if children exist," check the count in application code before calling delete. See docs/SOFT-DELETE.md for the soft-delete surface.

---

Self-referential FKs

A model with a relation to itself. The FK column lives on the same table that's referenced.

const Category = model('categories', {
  id:        f.id(),
  parent_id: f.objectId().optional(),
  name:      f.string(),
}).relate(() => ({
  parent:   rel.one('category',  { on: 'parent_id', refs: 'id', onDelete: 'SetNull' }),
  children: rel.many('category', { on: 'parent_id', refs: 'id' }),
}));

forge push emits a single fk_categories_parent_id constraint pointing at categories(id) — only the owning rel.one produces DDL.

Cascade choice on a tree

On Postgres / MySQL / MSSQL the cascade chain runs in-engine and depth is bounded only by the recursion limit (~1000 levels). SetNull is the safer default for trees that represent navigation / content — deleting a category promotes its children to root rather than wiping the subtree. Cascade is fine for audit trees where the children only make sense alongside their parent.

The Mongo walker's visited set (src/adapters/mongo/cascade.ts:103) tracks (collection, _id) pairs and skips already-seen rows, so self-referential cascades terminate.

Closure tables for fast ancestor queries

Self-referential FKs give you one level of parent at a time. For ancestor queries use a closure table or the WITH RECURSIVE CTE pattern from Self-referential relations.

---

Composite FKs

The SQL standard supports multi-column FKs. Forge's surface uses single-column on and refs strings, so composite FKs aren't expressible through rel.one. The workarounds:

Option A — flatten with a generated column

Flatten the composite into a deterministic column on both sides. The unique on the parent is what makes it a valid FK target:

const Tenant = model('tenants', {
  id:    f.id(),
  scope: f.string(),
  key:   f.string(),
  // Generated column — concatenation of scope + key, used as the FK target.
  compound: f.string().generated((c) => `${c.scope}|${c.key}`),
}).index({ name: 'tenants_compound_uniq', fields: ['compound'], unique: true });

const Resource = model('resources', {
  id:           f.id(),
  tenant_scope: f.string(),
  tenant_key:   f.string(),
  tenant_ref:   f.string().generated((c) => `${c.tenant_scope}|${c.tenant_key}`),
}).relate(() => ({
  tenant: rel.one('tenant', { on: 'tenant_ref', refs: 'compound', onDelete: 'Cascade' }),
}));

See docs/GENERATED-COLUMNS.md for the generated-column surface.

Option B — raw composite FK after push

For schemas you don't want to flatten, emit the composite FK via $executeRaw after the tables exist:

await db.$executeRaw`
  ALTER TABLE resources
    ADD CONSTRAINT fk_resources_tenant
      FOREIGN KEY (tenant_scope, tenant_key)
      REFERENCES tenants(scope, key)
      ON DELETE CASCADE
`;

The constraint won't show up in forge's relation graph — include, filters, and the cascade walker don't know about it — but the database enforces it. forge push won't flag this as drift on Postgres / MySQL / MSSQL; the introspector ignores constraints it didn't emit. On SQLite, any extra FK shows as drift — see docs/SQLITE.md for the suppression pattern.

Option C — app-layer validation

Validate at write time inside a transaction — the only option on Mongo, where the walker is single-column:

await db.$transaction(async (tx) => {
  const parent = await tx.tenant.findUnique({
    where: { scope_key: { scope: 's', key: 'k' } },
  });
  if (!parent) throw new Error('tenant not found');
  await tx.resource.create({ data: { tenant_scope: 's', tenant_key: 'k', ... } });
});

---

Cross-schema FKs

Postgres supports FKs across schemas of the same database. Forge's rel.one names a target schema key, not a fully qualified table; the emitter resolves the target via the runtime schema (src/adapters/postgres/ddl.ts:263) and emits an unqualified reference, which Postgres resolves via the search path.

Cross-database FKs — referencing a table in a different database, even on the same cluster — are not supported by any dialect. FKs are intra-database only. The workarounds:

• Logical separation (different schemas, same database) — FK works.
• Physical separation (different database, same cluster) — FK doesn't work; maintain integrity in application code.
• Different cluster / region — FK doesn't fit; you're in eventual-consistency territory.

Mongo's walker calls dbClient.db.collection(name) against the single database the connection points at; splitting schema across Mongo databases means cascade can't reach across. MSSQL allows three-part names (database.schema.table) but the FK syntax doesn't support crossing databases.

---

FK indexes — the child column is on you

The trap that most often shows up as "delete is slow."

Every dialect auto-indexes the parent column (the referenced PK or unique). None of them auto-index the child column — the FK column on the owning side. Without an index there, every parent delete has to full-scan the child table to find rows to cascade / restrict / null. For tiny tables you don't notice; for a posts table with millions of rows, deleting a single user fires a sequential scan against posts(author_id).

The fix

Declare an explicit index on the FK column:

const Post = model('posts', {
  id:        f.id(),
  author_id: f.objectId(),
})
  .index({ name: 'posts_author_id_idx', fields: ['author_id'] })
  .relate(() => ({
    author: rel.one('user', { on: 'author_id', refs: 'id', onDelete: 'Cascade' }),
  }));

The index name follows the <table>_<col>_idx convention. See docs/INDEXES.md for the full index surface.

Per-dialect specifics

MySQL InnoDB is the exception — it auto-creates an index on the FK column. Postgres / SQLite / MSSQL do not; declare manually. DuckDB doesn't index for OLTP-style point lookups anyway. Mongo — the walker uses find({ <fk>: { $in: [...] } }); declare via .index({ keys: { <fk>: 1 } }) on any non-tiny child collection.

The forge doctor surfaces missing FK indexes on Postgres and MySQL — a FK column with no covering index is flagged. See docs/DOCTOR.md.

---

Online FK add

Adding a FK to an existing table is mostly online on modern dialects, with one big caveat per dialect.

Postgres — NOT VALID + VALIDATE

The two-step pattern. Step 1 takes a short lock to add the constraint without validating existing rows; new inserts are checked from now. Step 2 validates existing rows under a SHARE UPDATE EXCLUSIVE lock — concurrent reads and writes continue:

await db.$executeRaw`
  ALTER TABLE posts
    ADD CONSTRAINT fk_posts_author_id
    FOREIGN KEY (author_id) REFERENCES users(id)
    ON DELETE CASCADE
    NOT VALID
`;
await db.$executeRaw`ALTER TABLE posts VALIDATE CONSTRAINT fk_posts_author_id`;

If validation finds an orphan it errors and the constraint stays NOT VALID. Fix the orphans and retry. Forge's standard forge push emits the full constraint at table create — NOT VALID is the online path for existing tables. The constraint name has to match what forge would emit so drift detection doesn't try to re-add it.

MySQL — online DDL by default

Adding a FK on InnoDB is online since 5.6 (ALGORITHM=INPLACE, concurrent DML allowed, brief metadata lock at start and end). If orphan rows exist the ALTER errors and rolls back. Backfill / delete the orphans first; FOREIGN_KEY_CHECKS = 0 skips validation but leaves bad data behind.

SQLite — full rebuild

SQLite's ALTER TABLE doesn't support adding constraints. The official workaround is a full table rebuild:

BEGIN;
CREATE TABLE posts_new (
  id TEXT PRIMARY KEY,
  author_id TEXT,
  title TEXT,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);
INSERT INTO posts_new SELECT * FROM posts;
DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;
COMMIT;

For large databases the INSERT ... SELECT blocks writes. Forge does not auto-orchestrate the rebuild — forge push surfaces the missing FK as drift and asks you to acknowledge it. The rebuild is a manual migration script.

MSSQL — WITH NOCHECK

Add the constraint without validating existing rows, then validate out of band:

ALTER TABLE posts
  WITH NOCHECK ADD CONSTRAINT fk_posts_author_id
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE posts WITH CHECK CHECK CONSTRAINT fk_posts_author_id;

Untrusted constraints aren't used by the optimizer for join elimination, so the trusted state matters for plans even though it doesn't matter for correctness.

DuckDB / Mongo

DuckDB accepts the DDL but doesn't enforce — the addition is metadata only. Mongo has no DDL; the cascade walker reads the schema at delete time, so a new relation participates as soon as the deploy ships. Existing orphans are left alone.

---

Dropping a FK

Mostly safe and online — it's a catalog update only, no data is touched. Postgres / MSSQL use DROP CONSTRAINT; MySQL uses DROP FOREIGN KEY:

await db.$executeRaw`ALTER TABLE posts DROP CONSTRAINT fk_posts_author_id`;   // pg / mssql
await db.$executeRaw`ALTER TABLE posts DROP FOREIGN KEY fk_posts_author_id`;  // mysql

forge push will re-add the constraint on the next run if the relation is still in the schema — drop the relation first. SQLite needs the same rebuild dance as adding. DuckDB accepts DROP CONSTRAINT with no data effects. On Mongo, remove the relation from the schema; the walker stops following it on the next deploy.

---

Cycles between tables

Two tables where each owns a FK pointing at the other — a users table with current_org_id pointing at orgs, and an orgs table with owner_user_id pointing at users. The chicken-and-egg insert problem: you can't insert a user without an org id, can't insert an org without an owner user id.

1. Make one side nullable. The "true" creator side is null at insert and filled in by a follow-up update inside a transaction:

const User = model('users', { id: f.id(), current_org_id: f.objectId().optional() })
  .relate(() => ({
    current_org: rel.one('org', { on: 'current_org_id', refs: 'id', onDelete: 'SetNull' }),
  }));

const Org = model('orgs', { id: f.id(), owner_id: f.objectId() })
  .relate(() => ({
    owner: rel.one('user', { on: 'owner_id', refs: 'id', onDelete: 'Restrict' }),
  }));

await db.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { current_org_id: null } });
  const org  = await tx.org.create({ data: { owner_id: user.id } });
  await tx.user.update({ where: { id: user.id }, data: { current_org_id: org.id } });
});

Each statement is individually valid — the null on user is fine because the FK column is nullable, and the org's owner_id points at a user that already exists.

2. Deferred constraints (Postgres only). Drop the non-deferrable constraint and re-add as DEFERRABLE INITIALLY DEFERRED. The check runs at commit; both sides can be inserted in any order inside a transaction. See Deferred constraint checking.

3. Drop the cycle. Most cycles indicate a domain modeling miss — "current org" is usually better as a memberships table with a primary flag than as a column on users. Refactoring is the long-term fix; the patterns above are short-term unblock.

Cycles plus Cascade on both sides would loop forever without the visited set in the Mongo walker (src/adapters/mongo/cascade.ts:103) — it tracks (collection, _id) pairs and terminates after one round each side.

---

Worked examples

(a) Post.user_id references User.id ON DELETE CASCADE

The canonical one-to-many with cascade, plus the explicit FK index that keeps deletes fast at scale.

import { f, model, rel } from 'forge-orm';

const User = model('users', {
  id: f.id(), email: f.string().unique(), name: f.string(),
}).relate(() => ({
  posts: rel.many('post', { on: 'author_id', refs: 'id' }),
}));

const Post = model('posts', {
  id: f.id(), author_id: f.objectId(),
  title: f.string(), body: f.text(),
  created_at: f.dateTime().default('now'),
})
  .index({ name: 'posts_author_id_idx', fields: ['author_id'] })
  .relate(() => ({
    author: rel.one('user', { on: 'author_id', refs: 'id', onDelete: 'Cascade' }),
  }));

forge push emits:

-- Postgres
ALTER TABLE "posts" ADD CONSTRAINT "fk_posts_author_id"
  FOREIGN KEY ("author_id") REFERENCES "users" ("id")
  ON DELETE CASCADE;
CREATE INDEX "posts_author_id_idx" ON "posts" ("author_id");

Without the explicit index, DELETE FROM users WHERE id = ? has to scan every row in posts to find children to cascade.

(b) Tree — Category.parent_id

Self-referential with SetNull so deleting a category promotes its children to root rather than wiping the subtree.

const Category = model('categories', {
  id:        f.id(),
  parent_id: f.objectId().optional(),
  name:      f.string(),
})
  .index({ name: 'categories_parent_id_idx', fields: ['parent_id'] })
  .relate(() => ({
    parent:   rel.one('category',  { on: 'parent_id', refs: 'id', onDelete: 'SetNull' }),
    children: rel.many('category', { on: 'parent_id', refs: 'id' }),
  }));

Loading the tree manually (breadth-first) or via WITH RECURSIVE — both patterns covered in the Self-referential relations section of RELATIONS.

(c) Composite FK via generated column

Flatten the composite into a deterministic string and FK against that. The unique on the parent's compound column makes it a valid FK target. See Composite FKs for the shape; option B (raw DDL after push) for composites that resist flattening.

(d) Mongo app-layer reference enforcement

Mongo has no FK enforcement at the storage layer. The cascade walker handles deletes, but inserts referencing a missing parent succeed silently. Validate in application code for parent-must-exist semantics on writes:

async function createPost(authorId: string, data: PostInput) {
  return db.$transaction(async (tx) => {
    const author = await tx.user.findUnique({ where: { id: authorId } });
    if (!author) throw new Error(`unknown author: ${authorId}`);
    return tx.post.create({ data: { ...data, author_id: authorId } });
  });
}

On a replica set the transaction pins both the read and write to one session. For constant integrity, run a periodic reconciliation:

const orphans = await db.post.aggregate([
  { $lookup: { from: 'users', localField: 'author_id', foreignField: '_id', as: '_author' } },
  { $match: { _author: { $size: 0 } } },
  { $project: { _id: 1 } },
]);

The same shape works on DuckDB — application-layer integrity is the only guarantee on either adapter.

---

Cross-links

• RELATIONS — relation shapes, rel.one / rel.many, nested writes, the four cascade actions on the surface.
• MODEL — model(...), f.id() / f.objectId(), how PKs flow into FK columns.
• MIGRATIONS — forge push, drift detection, FK add / drop / change in the schema vs the live DB.
• SOFT-DELETE — softDelete(), the deleted_at column, why FK cascades don't fire on soft delete, deleteHard.
• INDEXES — the index surface for FK column indexes (the child column is on you).
• SQLITE — custom drivers, PRAGMA foreign_keys, the rebuild dance for online FK changes.
• MONGO — the cascade walker, replica-set requirement for atomic cascades, $lookup for relation reads.